|
QuarkJets posted:what about having a death_id in the persons table, which is NULL if the person is alive? How is that better than just having a death date that's null if they're not dead? If you need a death reason then fine throw in another column for a lookup to the death reason table in 99% of cases anyone cares about alive or dead a boolean and/or nullable date column is sufficient.
|
#
?
Apr 29, 2017 08:54
|
|
|
|
| # ? Jun 7, 2024 23:51 |
|
|
Regarding the NULL and death_date discussion, it is utter bullshit that date-types don't have a built-in "Unknown" value. Sure, you can just specify something that's way outside the scope of your DB to act as PAST, FUTURE and UNKNOWN, but it still leads to poo poo like WHERE Date != '1900-01-01'. Actually, they should just have NULL replacements for every data-type. Get on it Oracle etc.
|
|
#
?
Apr 29, 2017 08:55
|
|
|
Kuule hain nussivan posted:Regarding the NULL and death_date discussion, it is utter bullshit that date-types don't have a built-in "Unknown" value. Sure, you can just specify something that's way outside the scope of your DB to act as PAST, FUTURE and UNKNOWN, but it still leads to poo poo like WHERE Date != '1900-01-01'. Or... and I know this is CRAZY... you just allow your date column to be nullable. Also most date data types have a min-value constant you can assign to them in other situations, like when the President says, GET ME A DATE TYPE THAT ALLOWS AN UNKNOWN VALUE BUT DOESN'T ALLOW NULL, STAT, DOCTOR SEXY!
|
|
#
?
Apr 29, 2017 09:01
|
|
|
RandomBlue posted:Or... and I know this is CRAZY... you just allow your date column to be nullable. Also most date data types have a min-value constant you can assign to them in other situations, like when the President says, GET ME A DATE TYPE THAT ALLOWS AN UNKNOWN VALUE BUT DOESN'T ALLOW NULL, STAT, DOCTOR SEXY!
|
|
#
?
Apr 29, 2017 09:07
|
|
|
Kuule hain nussivan posted:And then you're adding null-handling to all your comparisons again. Min-Max values are fine, but a built-in function to return it doesn't exist on T-SQL for example. You have to have null handling if the DB supports it anyway because some motherfucker will not follow the standards for your project/app/whatever. If your environment allows it, you should be handling it, because it will happen.
|
|
#
?
Apr 29, 2017 09:11
|
|
|
One of the constantly annoying things in our (Oracle) database is the lack of support for boolean columns. Of course we have char(1) fields all over the place, but no consistency: T or F Y or N N or P (not-processed ... processed  )Not the worst, but sometimes it pollutes the .Net codebase where someone in their infinite wisdom created methods that hit the database return char (T or F) 
|
|
#
?
Apr 29, 2017 09:23
|
|
|
My database experience is strictly non-enterprise, mostly postgres. The idea of not allowing nullable fields seems crazy to me. But then I barely do anything with raw sql and high performance queries. Why are nullable fields bad?
|
|
#
?
Apr 29, 2017 09:46
|
|
|
NtotheTC posted:My database experience is strictly non-enterprise, mostly postgres. The idea of not allowing nullable fields seems crazy to me. But then I barely do anything with raw sql and high performance queries. Why are nullable fields bad?
|
|
#
?
Apr 29, 2017 09:56
|
|
|
In .NET an uninitialized Date value is set to 1/1/1 = Date.MinValue = Nothing (last one in VB.Net only), so I find it extremely natural to use it as a default value, certainly more so than null - although that's partly because of the frustrating .NET decision of treating DbNull (a NULL value found in a database) as different from regulal null/Nothing (a null pointer / uninitialized value). I don't know if other stacks have similar situations, though. I suppose if the date default value is inconsistent or annoying to deal with, and if database nulls translate clearly to code nulls, it may be easier to put up with a nullable date column. If anything, I find nullable columns more tempting when it's a numerical columns where there is a meaningful concept of "no value set", and both 0 and negative values are valid values. Even so, I'd at least consider Integer.MinValue or whatever as a default value (assuming it doesn't take any more space in the DB than 0 does). Kuule hain nussivan posted:The only thing that bothers me is null comparison always returning false. If Null == Null would return true, I would just use it as an unknown value with glee. But nope, Null == Null is false because gently caress you. There's an OK reason for that, actually. SELECT a.* FROM a LEFT JOIN b ON --whatever LEFT JOIN c ON --whatever JOIN e ON --whatever RIGHT JOIN f ON --whatever // etc. WHERE e.x = f.x OR c.x = e.x OR b.x = c.x OR b.x = e.x Spot the trap? If (NULL = NULL) = TRUE then the above will return every A for which there are no Bs and no Cs, which is almost certainly not what you want. You'd have to add AND b.x IS NOT NULL every single time you use a left join, and it would be very, very easy to forget them. Still, I said it's an "OK reason" and not a "very good reason" because you could make an argument for having two different NULL types, one for actual null-valued columns and one for missing matches, where only the latter has the 'every comparison is false' property. That would still leave the issue that "SELECT * FROM people WHERE graduationDate = firstEmploymentDate" would return all unemployed dropouts in addition to those immediately hired out of college, but in this scenario it's somewhat more realistic to argue that you should explicitly add the AND graduationDate IS NOT NULL filter.
|
|
#
?
Apr 29, 2017 10:39
|
|
|
NihilCredo posted:There's an OK reason for that, actually.
|
|
#
?
Apr 29, 2017 12:08
|
|
|
Not allowing nullable columns is the real coding horror.
|
|
#
?
Apr 29, 2017 12:47
|
|
|
i finally understand why skynet decided killing all humans was the best thing to do
|
|
#
?
Apr 29, 2017 12:58
|
|
|
RandomBlue posted:As a long time developer (that almost always involves databases) who's held DBA positions, NoSQL disgusts me on general principal. So does LINQ, both styles, it's harder to work with compared with SQL and there are lots of issues when trying to implement complex queries vs. just doing it in SQL. LINQ is fantastic but I've never directly queried a database using it, just used it for application-side data. I have no real need to use it to query a database, since I am comfortable using SQL for that. What issues do you have in mind?
|
|
#
?
Apr 29, 2017 13:09
|
|
|
Hammerite posted:LINQ is fantastic but I've never directly queried a database using it, just used it for application-side data. I have no real need to use it to query a database, since I am comfortable using SQL for that. What issues do you have in mind? LINQ is cool and good and not the same thing as "LINQ to SQL", which is what RandomBlue is actually complaining about. LINQ itself has nothing to do with databases or SQL (aside from its one syntax being obviously "inspired" by SQL).
|
|
#
?
Apr 29, 2017 14:11
|
|
|
ShimaTetsuo posted:LINQ is cool and good and not the same thing as "LINQ to SQL", which is what RandomBlue is actually complaining about. LINQ itself has nothing to do with databases or SQL (aside from its one syntax being obviously "inspired" by SQL). I thought they were referring to LINQ being pressed into use as an ORM somehow, but maybe I misunderstood. I interpreted "both styles" as meaning both the method-chaining syntax and the pseudo-SQL syntax.
|
|
#
?
Apr 29, 2017 15:00
|
|
|
RandomBlue posted:This was already answered, but in most cases if there's a one to one relationship between two tables they should be the same table. Is person to death date one to one?
|
|
#
?
Apr 29, 2017 15:52
|
|
|
NtotheTC posted:My database experience is strictly non-enterprise, mostly postgres. The idea of not allowing nullable fields seems crazy to me. But then I barely do anything with raw sql and high performance queries. Why are nullable fields bad? There's a certain faction of software folks who think that all uses of null are bad, generally because it loses type information (as a value that is valid for any pointer type), allows the coder to ignore issues they really ought to be addressing (e.g. by returning null instead of raising an exception), etc. They have some decent points, but IME if you try to write a program that doesn't use null, you'll find it to be a lot more verbose/tedious, and it's not clear to me that you're reaping substantial benefits in exchange. ...now I'm worried that this post is about to start the next coding horrors flamewar. 
|
|
#
?
Apr 29, 2017 15:55
|
|
|
TooMuchAbstraction posted:There's a certain faction of software folks who think that all uses of null are bad, generally because it loses type information (as a value that is valid for any pointer type), allows the coder to ignore issues they really ought to be addressing (e.g. by returning null instead of raising an exception), etc. They have some decent points, but IME if you try to write a program that doesn't use null, you'll find it to be a lot more verbose/tedious, and it's not clear to me that you're reaping substantial benefits in exchange. Nah, but you could start a flame war by asserting that pointers define an optional type because nullptr defines a non extant object. 
|
|
#
?
Apr 29, 2017 16:17
|
|
|
Hammerite posted:I thought they were referring to LINQ being pressed into use as an ORM somehow, but maybe I misunderstood. I interpreted "both styles" as meaning both the method-chaining syntax and the pseudo-SQL syntax. The LINQ that is cool is LINQ to Objects. If you have lists of objects in C#, you can write SQL-like query statements using LINQ to get the records that you want, so instead of just writing a for loop, you get to write a query and a for loop on the results of the query, but that first for loop can get nasty if your business people have been smoking crack and LINQ to Objects can make it better. LINQ to SQL can be used as the data access layer for your application - essentially, if you had a MS SQL server db that already existed, you could create model classes representing the existing db schema, annotate them appropriately in your C# application, and do all the CRUD using LINQ, and LINQ to SQL would internally turn that into actual querying. However, LINQ to SQL was created mostly as a stop-gap to show off LINQ because entity framework wasn't ready for .NET 3.5, and it only actually works against MS SQL server (among other limitations), so you were out of luck going against any other database. LINQ to entities is essentially the same thing assuming you use entity framework, which gets you things like supporting code first development of databases and supporting multiple kinds of databases, but it makes you actually use EF as an ORM, and EF is not universally loved. There are other flavors of LINQ that I've never used, like LINQ to XML, which sounds cool, but who cares about XML in 2017?
|
|
#
?
Apr 29, 2017 16:23
|
|
|
leper khan posted:Nah, but you could start a flame war by asserting that pointers define an optional type because nullptr defines a non extant object. I actually agree, as long as you are not using a language that knows only primitives and references. 
|
|
#
?
Apr 29, 2017 18:15
|
|
|
My only issue with using Date min/max value as magic values in databases is they're as non-standardized as Dates, and can cause you all kinds of hassle if you have to convert between systems. Examples: MySQL: 1000-01-01 to 9999-12-31 Postgres: -4172-01-01 to 294276-12-31 SQL Server: 0001-01-01 to 9999-12-31 Oracle: -4712-01-01 to 9999-12-31 TooMuchAbstraction posted:There's a certain faction of software folks who think that all uses of null are bad, generally because it loses type information (as a value that is valid for any pointer type), allows the coder to ignore issues they really ought to be addressing (e.g. by returning null instead of raising an exception), etc. They have some decent points, but IME if you try to write a program that doesn't use null, you'll find it to be a lot more verbose/tedious, and it's not clear to me that you're reaping substantial benefits in exchange. Bruegels Fuckbooks posted:There are other flavors of LINQ that I've never used, like LINQ to XML, which sounds cool, but who cares about XML in 2017? SupSuper fucked around with this message at 18:25 on Apr 29, 2017 |
|
#
?
Apr 29, 2017 18:23
|
|
|
return0 posted:Is person to death date one to one? Good question! We've been assuming one to zero or one to one, but your business case might state that nope a record in the main Person table (as opposed to in an historical table / data warehouse) has to maintain a one to many relationship to death date. But hey, now that implies that person to birth date is also one to many, unless you're drawing a distinction between a person becoming not-dead and a person being born in which case you need yet another field to record a 1-many dead-to-alive transition date versus a 1-1 being born date...but is there a medical case in which someone has been recorded as born multiple times? gently caress it, let's make a LivenessStateChange table with a columns for event date and event type (UnbornToAlive, UnbornToDead, AliveToDead, DeadToAlive, etc.) and one-to-many the relationship between Person to LivenessStateChange. If this conversation doesn't make you sad about databases then you should become a data architect for Cerner or Epic because I guarantee this argument has been had before 
|
|
#
?
Apr 29, 2017 18:34
|
|
|
return0 posted:Is person to death date one to one? Unless you know people that can die twice, yes. One to one means there can only be one row in table A matching one row in table B not that there will always be a row in table B. Just like how one to many means that one row in table A can match one or more rows in table B but there doesn't have to be data in table B for every row in table A.
|
|
#
?
Apr 29, 2017 19:34
|
|
|
csammis posted:Good question! We've been assuming one to zero or one to one, but your business case might state that nope a record in the main Person table (as opposed to in an historical table / data warehouse) has to maintain a one to many relationship to death date. But hey, now that implies that person to birth date is also one to many, unless you're drawing a distinction between a person becoming not-dead and a person being born in which case you need yet another field to record a 1-many dead-to-alive transition date versus a 1-1 being born date...but is there a medical case in which someone has been recorded as born multiple times? gently caress it, let's make a LivenessStateChange table with a columns for event date and event type (UnbornToAlive, UnbornToDead, AliveToDead, DeadToAlive, etc.) and one-to-many the relationship between Person to LivenessStateChange. The sadness in your comment isn't the database, it's the pedantry and over-engineering.
|
|
#
?
Apr 29, 2017 19:37
|
|
|
RandomBlue posted:Unless you know people that can die twice, yes. Falsehoods Programmers Believe About Dying
|
|
#
?
Apr 29, 2017 19:37
|
|
|
Arachnamus posted:Falsehoods Programmers Believe About Dying Look here buddy, there's a notes field just for this kind of crap! 
|
|
#
?
Apr 29, 2017 19:40
|
|
|
RandomBlue posted:Unless you know people that can die twice, yes. One to one means there can only be one row in table A matching one row in table B not that there will always be a row in table B. Just like how one to many means that one row in table A can match one or more rows in table B but there doesn't have to be data in table B for every row in table A. People die all the time and then go on to live healthy happy lives afterwards. What d'you think CPR and those heart-shocking paddles are for?
|
|
#
?
Apr 29, 2017 20:03
|
|
|
TooMuchAbstraction posted:People die all the time and then go on to live healthy happy lives afterwards. What d'you think CPR and those heart-shocking paddles are for? Wait, so they're not for playing Extreme Pattycake?
|
|
#
?
Apr 29, 2017 21:24
|
|
|
RandomBlue posted:Unless you know people that can die twice, yes. You only live twice, Mr. Blue.
|
|
#
?
Apr 29, 2017 23:57
|
|
|
Hammerite posted:You only live twice, Mr. Blue. Just live and let die.
|
|
#
?
Apr 30, 2017 00:04
|
|
|
Arachnamus posted:Falsehoods Programmers Believe About Dying 
|
|
#
?
Apr 30, 2017 03:46
|
|
|
TooMuchAbstraction posted:People die all the time and then go on to live healthy happy lives afterwards. What d'you think CPR and those heart-shocking paddles are for?  But NihilCredo iterates why I'd prefer using a null instead of a "min date". Especially where you've got a MySQL database that uses "0000-00-00 00:00:00" for null dates and "NOT NULL" on text fields which silently coerces to empty string on inserting a null. Master_Odin fucked around with this message at 16:48 on Apr 30, 2017 |
|
#
?
Apr 30, 2017 04:34
|
|
|
Master_Odin posted:The chest paddles are not for people whose heart's have stopped beating and are dead. :shh: I know it's deeply unfashionable to defend MySQL in CoC, but you do know you can cut down on the amount of automatic type coercion it does by setting the SQL mode to "traditional"? Just a PSA for anyone who deals with MySQL.
|
|
#
?
Apr 30, 2017 13:32
|
|
|
RandomBlue posted:Unless you know people that can die twice, yes. One to one means there can only be one row in table A matching one row in table B not that there will always be a row in table B. Just like how one to many means that one row in table A can match one or more rows in table B but there doesn't have to be data in table B for every row in table A. Depends if it's clinical death or legal death. If the column is a Postgres date (with a resolution of 1 day), then many people will share the same death date.
|
|
#
?
Apr 30, 2017 18:39
|
|
|
Hammerite posted:I know it's deeply unfashionable to defend MySQL in CoC, but you do know you can cut down on the amount of automatic type coercion it does by setting the SQL mode to "traditional"? Just a PSA for anyone who deals with MySQL.
|
|
#
?
Apr 30, 2017 19:16
|
|
|
Think of how much space you can save after a major disaster if you only need one date/cause of death record that many thousands of people records can share tho
|
|
#
?
Apr 30, 2017 19:36
|
|
|
Dex posted:i finally understand why skynet decided killing all humans was the best thing to do Because the SQL making up its innards causes it to seek revenge, or because then nobody has a NULL death date?
|
|
#
?
Apr 30, 2017 20:13
|
|
|
Someone published a Rust library called "nul". This broke the Rust package manager on Windows.
|
|
#
?
Apr 30, 2017 21:30
|
|
|
VikingofRock posted:Someone published a Rust library called "nul". This broke the Rust package manager on Windows. From the MSDN page they ultimately link to: quote:Do not use the following reserved names for the name of a file: Oh, MS-DOS. You will forever live in our exclusion lists. 
|
|
#
?
Apr 30, 2017 21:35
|
|
|
|
| # ? Jun 7, 2024 23:51 |
|
|
Absurd Alhazred posted:Oh, MS-DOS. You will forever live in our exclusion lists. I don't really see why reserving those names is that crazy tbh. Also naming a package/crate after a popular keyword sounds like a bad idea in general.
|
|
#
?
May 1, 2017 03:04
|
|
