Database Normalization Question

Status
Not open for further replies.

YourUncleBob

Junior Member
Sep 27, 2009
8
0
0
Hi everyone thanks for reading

I have inherited a problem, I need to merge these two databases but one is a single table and the other is a set of 3 tables.

First here is our database
http://imgur.com/jp0kp.png

Here is their database
http://imgur.com/OVZGB.jpg

How would you go about doing this? Their database uses the PARENT_MODEL column to tie makes and models together, is this an appropriate way that I should duplicate in our database?

Thanks for reading, Further down the road we'll probably pick up another chunk of information so I would like to get it right this time.
 

PhatoseAlpha

Platinum Member
Apr 10, 2005
2,131
21
81
The data showing in their database seems a little...nonsensical to me. PARENT_MODEL doesn't seem to actually reference make at all - the MAKE_ID column in the MODELS table does that. PARENT_MODEL actually seems to reference another row in MODELS. But then you look at the ITEMS table, and it makes very little sense - they only seem to reference the child models, not the parents.


Do some models actually have parent models where this data would be worth storing?
 

YourUncleBob

Junior Member
Sep 27, 2009
8
0
0
Those are the things that make me nervous. Here is a look at the lookup on the website vs the database

http://imgur.com/jUMMp.jpg

Basically they have model and submodel in the same table but a top level model has a parent model of 0 while a submodel has a parent model of some other model. This is the weirdest thing I've found in their data so far.

My instinct would have been to do
make - makeid
model -modelid
submodel - submodelid

makeid - modelid
modelid - submodelid

and then have the items table be makeid modelid submodelid and so on but if this is bad practice then it'll burn me later on.
 

beginner99

Diamond Member
Jun 2, 2009
5,315
1,760
136
Basically they have model and submodel in the same table but a top level model has a parent model of 0 while a submodel has a parent model of some other model. This is the weirdest thing I've found in their data so far.

That's a recursive relationship.

http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php

Of course whoever made it made a noob mistake. putting in a 0 in that field if the record has no parentmodel is of course plain wrong. it should be NULL instead.

I suggest you create a model that is good without regarding the current 2 databases. But hard to tell what is good if I don't really have an idea what you are trying to achieve.

Your table is of course not normalized. So 1 table is not enough.
What is a "make"? Isn't an acura a Honda Model? Seems you have mixed models and manufacturers ("car brand").
 

YourUncleBob

Junior Member
Sep 27, 2009
8
0
0
Well Acura is a Make in the Year->Make->Model->Style->Part typical lookup sequence. I haven't heard of a recursive relationship so after this reply I will be following your link and reading up on it.

The end goal is that our company produces auto parts and we need to tie our part to what vehicles they fit. The end user needs to be able to select their vehicle and drill down to find our parts.

Our catalog is setup as 2 tables, one for vehicle information with each unique
year make model style location part
On it's on field. When a user enters the search it do a SELECT YEAR from VEHICLE DISTINCT and then SELECT MAKE from VEHICLE WHERE YEAR = (year) DISTINCT, etc. This works fine because of mysql's speed and indexing but will make it impossible for us to link data with other databases.

The data we have inherited covers a lot of euro vehicles, however their database is what i've been describing. Instead of stitching up their data to match our and increasing the vehicle table from 60k records to 80k records i'd like to go ahead and convert both sets to something that is correct.

So all of that to say this. I am new to this and as such just try to do right by what I am taught. I *think* that what I want to do is have a table of makes - makeID, a table of models-modelID, etc but in the end won't I just have one big table of:
1999 - 14 - 38 - 838 - Left Front - Part? This table would still be the full 60k records so what's the point? I know that comparing two numbers is much faster than comparing text strings but is that the only reason?

Any input would be helpful even if it's to say that i'm out of my mind. Thanks for the reply.
 

PhatoseAlpha

Platinum Member
Apr 10, 2005
2,131
21
81
There are a number of advantages.

For one, numbers are not only faster, they take up less space. A 4-byte integer can reference a string, which can take up a good bit more space in memory and on the hard drive.

Secondly, doing it this way ensures that you don't end up dealing with duplication due to typos or mispellings - you're not gonna get a part in your database twice, once with the model spelled "Taureg" and once spelled "Touregg", and so forth.


Additionally, remember you can remove down the final amount of information stored in the parts label. The ModelId should reference the make ID, so if you have the ModelID, you have the MakeID, meaning you don't need it. The Style ID will reference MakeID, so you don't need to store MakeID if you have StyleID.

You'd end up with tables like:
MAKES: MakeID, MakeName
MODELS: ModelID, ModelName, MakeID
STYLES: StyleID, StyleName, ModelID
Parts: PartID, StyleID, Name, ect, ect.

Getting the data back is a simple join.

This also has the benefit of making it easy to have foreign key restraints, so you don't end up with Parts referencing styles that don't exist, and so forth.
 

Shilohen

Member
Jul 29, 2009
194
0
0
Well Acura is a Make in the Year->Make->Model->Style->Part typical lookup sequence. I haven't heard of a recursive relationship so after this reply I will be following your link and reading up on it.

The end goal is that our company produces auto parts and we need to tie our part to what vehicles they fit. The end user needs to be able to select their vehicle and drill down to find our parts.

Our catalog is setup as 2 tables, one for vehicle information with each unique
year make model style location part
On it's on field. When a user enters the search it do a SELECT YEAR from VEHICLE DISTINCT and then SELECT MAKE from VEHICLE WHERE YEAR = (year) DISTINCT, etc. This works fine because of mysql's speed and indexing but will make it impossible for us to link data with other databases.

The data we have inherited covers a lot of euro vehicles, however their database is what i've been describing. Instead of stitching up their data to match our and increasing the vehicle table from 60k records to 80k records i'd like to go ahead and convert both sets to something that is correct.

So all of that to say this. I am new to this and as such just try to do right by what I am taught. I *think* that what I want to do is have a table of makes - makeID, a table of models-modelID, etc but in the end won't I just have one big table of:
1999 - 14 - 38 - 838 - Left Front - Part? This table would still be the full 60k records so what's the point? I know that comparing two numbers is much faster than comparing text strings but is that the only reason?

Any input would be helpful even if it's to say that i'm out of my mind. Thanks for the reply.

The 4 tables proposed by PhatoseAlpha is the way to go. Another advantage will be for the list of values. It's be easier to list the possible model for a given make and style for a specified model.

That being said, your part table seems to always fit the root model in the 3 tables screenshot, never the sub model. If so, then you might have some problems. You might have to duplicate rows in the part table for every style of a given model if a part fits for all models. It may or not be a good thing. If you want to limit the amount of rows, you could denormalize the part table a bit to have two columns model_id and style_id, both nullable but with a multi column check constraint on both to always have one not null. Personally, I prefer the first option, even if its duplicates rows.
 

EricMartello

Senior member
Apr 17, 2003
910
0
0
To the OP...did someone actually hire you? I mean this is kinda basic shit your asking here...I hope you're working for free.

Delete this, it's wrong: http://i.imgur.com/jp0kp.png

You don't need to change it much: http://i.imgur.com/OVZGB.jpg

The proper way to do this is to create one table for each "component".

1) Car Makes (Make ID [PRI] | Make Name)
2) Car Models (Model ID [PRI] | Make ID | Model Name)
3) Parts (Part ID [PRI] | Part Number | Part Desc)
4) Glue Table (Glue ID [PRI] | Make ID | Model ID | Part ID) - Links parts to models (and makes if desired).

And before you ask, because I know you will, you need table #4 because some parts can be shared among multiple cars, so with the glue table you can link them without having to duplicate them in the database.

I see no reason to use recursion in this situation since you are only going 1 level deep Make > Model...if you need to go deeper than 1 level you will need to use recursion which is probably over your head. But Walmart is always looking for people who excel at saying "Hello".



Commander Obvious XXVI strikes yet again...KA-CHOW

Of course whoever made it made a noob mistake. putting in a 0 in that field if the record has no parentmodel is of course plain wrong. it should be NULL instead.

NULL is 0, broski...I think the only noob mistake made in this case was you using google to make believe like you know what you're talking about...but not bothering to google the fact that NULL and 0 are interchangeable...and generally it is better to use 0.
 

Markbnj

Elite Member <br>Moderator Emeritus
Moderator
Sep 16, 2005
15,682
14
81
www.markbetz.net
NULL is 0, broski...I think the only noob mistake made in this case was you using google to make believe like you know what you're talking about...

NULL is not 0. 0 is a scalar value. NULL is the lack of a value. A foreign key should never be set to 0 to indicate the lack of a relationship, if for no other reason than that a client is unlikely to count wrong and pass NULL as a relationship key. Why would you pick 0 in any case? 0 is the first element of every array I've worked with. Why not -1? NULL is a much better choice, and it is semantically the correct choice.
 

Cogman

Lifer
Sep 19, 2000
10,284
138
106
NULL is 0, broski...I think the only noob mistake made in this case was you using google to make believe like you know what you're talking about...but not bothering to google the fact that NULL and 0 are interchangeable...and generally it is better to use 0.
Bad day? Just want to echo what mark said. Null != 0. MAYBE in c++ and c where it is defined as 0 you could get away with saying that, but in SQL and database land, that is not so.

NULL is no value, 0 is a value. If you put NULL in a persons salary, it means that a wage wasn't set. If you put 0 there, it means you aren't paying them. There is a difference. Kind of like if you put zero in as the number of sales someone has made, then they haven't made any sales, but null means the data isn't present/available to the database (so they could have made 1000 sales)
 

Leros

Lifer
Jul 11, 2004
21,867
7
81
Learn both. The language is just tool. Most of the concepts you'll learn are language independent.
 

Markbnj

Elite Member <br>Moderator Emeritus
Moderator
Sep 16, 2005
15,682
14
81
www.markbetz.net
MAYBE in c++ and c where it is defined as 0 you could get away with saying that, but in SQL and database land, that is not so

You know C/C++ better than I do, but I seem to recall that NULL == 0 was a compiler-implemented convenience, and that it shouldn't be assumed from the language definition.
 

Cogman

Lifer
Sep 19, 2000
10,284
138
106
You know C/C++ better than I do, but I seem to recall that NULL == 0 was a compiler-implemented convenience, and that it shouldn't be assumed from the language definition.
NULL in C and C++ is a pointer to the 0 address. The general implementation is just a #define NULL 0, While I guess it could be something else, it has to end up being interpreted as a pointer to the 0 address.

This is pretty critical as you don't want a memory pointer going to some other place in the OS/program. and since it is somewhat impossible to predict what platform, and hence what memory addresses will be in the program, the authors of C and C++ have defined NULL to always mean the 0 address. (which effectively translates into 0 = NULL).

In other languages it may be different (such as SQL). But in the C and C++ it is the standard. Non-conformant compilers might do something different :p. However the standard says NULL has an address of 0.

Interestingly enough, I was reading up on some of the attacks on the kernel, and one of them is a NULL pointer dereferencing attack. Especially bad if it happens in the kernel. Generally in linux, a dereferenced NULL pointer results in a flaming segment fault. However, it is possible to map the null pointer to user space. Thus, one of the attacks is to try and get the kernel to dereference a null pointer and use that to hijack the system. This is mostly solved through some privilege juggling, however, it is still a potential threat. The moral being that you should always check that a pointer isn't null (that can be null) before dereferencing it, and not rely on a crash and burn.

I'm sure degibson will fly in and correct any errors in what I've said :p He has a deeper understanding of C/C++ stuff then I do.
 

EricMartello

Senior member
Apr 17, 2003
910
0
0
Bad day? Just want to echo what mark said. Null != 0. MAYBE in c++ and c where it is defined as 0 you could get away with saying that, but in SQL and database land, that is not so.

NULL is no value, 0 is a value. If you put NULL in a persons salary, it means that a wage wasn't set. If you put 0 there, it means you aren't paying them. There is a difference. Kind of like if you put zero in as the number of sales someone has made, then they haven't made any sales, but null means the data isn't present/available to the database (so they could have made 1000 sales)

NULL and zero are interchangeable, even in a database. While I understand what you are saying, in this case, it is irrelevant because the parent_id is not referring to a quantity of something, i.e. salary or weight. It is simply an identifier and as such you can use null or 0, zero being preferred especially if you want other people working on this app.

Now if the field in question was actually counting something and not acting as an index, then you may want to use NULL if you want to indicate a total lack of a value for that particular field...however the application side of things would probably be designed to interpret the value 0 as nothing if that is what the developer was shooting for.

There is nothing wrong with using zero to indicate the top level of a recursive tree...so yes, you can use zero or NULL in this case and it will work just fine.
 

EricMartello

Senior member
Apr 17, 2003
910
0
0
NULL is not 0. 0 is a scalar value. NULL is the lack of a value. A foreign key should never be set to 0 to indicate the lack of a relationship, if for no other reason than that a client is unlikely to count wrong and pass NULL as a relationship key. Why would you pick 0 in any case? 0 is the first element of every array I've worked with. Why not -1? NULL is a much better choice, and it is semantically the correct choice.

Let's try something here: stay in context of this topic.

In the context of this topic, 0 is being used as an index value to indicate top level categories in what looked to be a recursive database structure in the works. There is no ill-effect for using 0, and using NULL is simply preference. If ALL the categories have a numerical ID, then let's be consistent and give the top level ones a numerical ID as well...that being zero.

Since the data is queried by an application, the application's designer should be well aware of the value he chose to represent top-level categories...i.e. zero. He could have used null, but he was not wrong for using zero. It's not indicating a lack of relationship; it's indicating its position within the relationship where zero = top = first...oh wait, that is striking similar to your comment about zero being the first element in an array.
 

Markbnj

Elite Member <br>Moderator Emeritus
Moderator
Sep 16, 2005
15,682
14
81
www.markbetz.net
Let's try something here: stay in context of this topic.

In the context of this topic, 0 is being used as an index value to indicate top level categories in what looked to be a recursive database structure in the works. There is no ill-effect for using 0, and using NULL is simply preference. If ALL the categories have a numerical ID, then let's be consistent and give the top level ones a numerical ID as well...that being zero.

Since the data is queried by an application, the application's designer should be well aware of the value he chose to represent top-level categories...i.e. zero. He could have used null, but he was not wrong for using zero. It's not indicating a lack of relationship; it's indicating its position within the relationship where zero = top = first...oh wait, that is striking similar to your comment about zero being the first element in an array.

You're getting the semantics wrong. If the column in question were counting the depth of a tree, or any other scalar magnitude, then 0 would be a valid value. In this case it is a foreign key. 0 is not a valid means of indicating the lack of a relationship between a parent table and a dependent table. Foreign keys can be used recursively to store multiple levels of a hierarchy in a single table, but that use doesn't magically transform a key into a scalar value.
 

EricMartello

Senior member
Apr 17, 2003
910
0
0
You're getting the semantics wrong. If the column in question were counting the depth of a tree, or any other scalar magnitude, then 0 would be a valid value. In this case it is a foreign key. 0 is not a valid means of indicating the lack of a relationship between a parent table and a dependent table. Foreign keys can be used recursively to store multiple levels of a hierarchy in a single table, but that use doesn't magically transform a key into a scalar value.

You love that word scalar, and yet you don't seem to know what it means. Do you often use keys with non-scalar values? lol I don't really know why you'd even bother to bring it up. It seems like you're trying to make this into something it's not, as if to justify your original incorrect response...watch closely:

Table 1:
category_id | category_name

Table 2:
subcat_id | category_id | parent_id

That is your table setup - all values are unsigned int, aside from category name which can be varchar or text. That is all you need to create a tree-style recursive structure with "infinite" sublevels, and the ability to assign multiple parent categories to a single category.

You can now define the top-level categories' parent_id with any int value you want, including 0, provided that you do not use that value as a category_id...so yes, you can use 0 and it will work PERFECTLY assuming the application is coded correctly.

It's really that simple...and as any good programmer knows, simplest is almost always best way to solve a problem. Clearly your attempt at obfuscation suggests you really don't know what you are doing or talking about here.
 

Cogman

Lifer
Sep 19, 2000
10,284
138
106
You love that word scalar, and yet you don't seem to know what it means. Do you often use keys with non-scalar values? lol I don't really know why you'd even bother to bring it up. It seems like you're trying to make this into something it's not, as if to justify your original incorrect response...watch closely:

Table 1:
category_id | category_name

Table 2:
subcat_id | category_id | parent_id

That is your table setup - all values are unsigned int, aside from category name which can be varchar or text. That is all you need to create a tree-style recursive structure with "infinite" sublevels, and the ability to assign multiple parent categories to a single category.

You can now define the top-level categories' parent_id with any int value you want, including 0, provided that you do not use that value as a category_id...so yes, you can use 0 and it will work PERFECTLY assuming the application is coded correctly.

It's really that simple...and as any good programmer knows, simplest is almost always best way to solve a problem. Clearly your attempt at obfuscation suggests you really don't know what you are doing or talking about here.
dude, You might want to back off a bit.
http://en.wikipedia.org/wiki/Null_%28SQL%29
http://en.wikipedia.org/wiki/Null_pointer#Null_pointer

You might be able to use 0 and have it work perfectly. However, it is not ideal. (Mark's original point) a NULL is ideal. You are making the database do more work by putting a 0 instead of a NULL there (The database still has to look up the zero value, it doesn't look up NULL values). That it still works isn't the issue. The issue is that you are using more indexing space, and more processing power then is necessary. While early optimizations should be avoided, this is a case that is so simple, it is dumb not to do.

As for the statements that 0 = NULL, see the links above. They aren't the same thing. Heck, a SQL_ANSI NULL and 0 aren't even treated the same. Try NULL= NULL, it should return false. Now try 0 = 0; It returns true. Different behavior because they aren't the same thing.
 
Last edited:

EricMartello

Senior member
Apr 17, 2003
910
0
0
dude, You might want to back off a bit.
http://en.wikipedia.org/wiki/Null_%28SQL%29
http://en.wikipedia.org/wiki/Null_pointer#Null_pointer

You might be able to use 0 and have it work perfectly. However, it is not ideal. (Mark's original point) a NULL is ideal. You are making the database do more work by putting a 0 instead of a NULL there (The database still has to look up the zero value, it doesn't look up NULL values). That it still works isn't the issue. The issue is that you are using more indexing space, and more processing power then is necessary. While early optimizations should be avoided, this is a case that is so simple, it is dumb not to do.

As for the statements that 0 = NULL, see the links above. They aren't the same thing. Heck, a SQL_ANSI NULL and 0 aren't even treated the same. Try NULL= NULL, it should return false. Now try 0 = 0; It returns true. Different behavior because they aren't the same thing.

Oh my...links to wikipedia...this is what we call grasping at straws...you two would be better served simply admitting that you were wrong, and not trying to drive this off topic with irrelevant and incorrect information about the usage of NULL and 0. Did I say they were equal or did I say there were INTERCHANGEABLE? Yes...NULL and ZERO are interchangeable. Period.

You may have certain conditions where using NULL to represent a lack of value is necessary, but you can always code the application to treat 0 as "lack of value". So it really boils down to coding style and preference.

Your statement about the database "having to do more work" if zero is used in place of null is just double-facepalm worthy. Which of these is faster:

SELECT * WHERE parent_id IS NULL;

SELECT * WHERE parent_id = 0;

(NOTE: you cannot use arithmetic comparators with NULL values...another minus for this situation because you would need one query statement for the top-level and another for the subcats...so you're needless filling up query cache and bloating your code all in the name of aesthetics - good job, guys)

Null values are included in the index if you index the key, but the DB still has to scan to find NULL values so performance wise there is little or no difference and you give up the functionality provided by using an integer.

Let me direct you to this relevant link:
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Now read this part carefully:
"In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1."

So, IN THE CONTEXT OF THE OP'S QUESTION, you can use EITHER null or zero, because you are saying that if the parent_id is 0, it has no parent, and therefore is "false".

I don't know how to dumb this down anymore for you guys...really...you're killin' me here. Do I have to bust out MS Paint and start drawing pictures or what?
 

beginner99

Diamond Member
Jun 2, 2009
5,315
1,760
136
You may have certain conditions where using NULL to represent a lack of value is necessary, but you can always code the application to treat 0 as "lack of value". So it really boils down to coding style and preference.

Using 0 to represent lack of value is plain wrong. lack of value = NULL.

Show me 1 link about database theory were the claim to use 0 for lack of value. Good luck with it.

If you put in 0 instead of NULL into a foreign key field that has no according entry in the foreign table breaks referntial Integrity.

Besides that 0 is used in Aggregate functions like Average. NULL is ignored.
So AVG of
NULL, 10 -> 10
AVG of
0, 10 -> 5

It's not the same and it is also not interchangeable.

Using 0 instead of NULL is just wrong.
 

Cogman

Lifer
Sep 19, 2000
10,284
138
106
Oh my...links to wikipedia...this is what we call grasping at straws...you two would be better served simply admitting that you were wrong, and not trying to drive this off topic with irrelevant and incorrect information about the usage of NULL and 0. Did I say they were equal or did I say there were INTERCHANGEABLE? Yes...NULL and ZERO are interchangeable. Period.

You may have certain conditions where using NULL to represent a lack of value is necessary, but you can always code the application to treat 0 as "lack of value". So it really boils down to coding style and preference.

Your statement about the database "having to do more work" if zero is used in place of null is just double-facepalm worthy. Which of these is faster:

SELECT * WHERE parent_id IS NULL;

SELECT * WHERE parent_id = 0;

(NOTE: you cannot use arithmetic comparators with NULL values...another minus for this situation because you would need one query statement for the top-level and another for the subcats...so you're needless filling up query cache and bloating your code all in the name of aesthetics - good job, guys)

Null values are included in the index if you index the key, but the DB still has to scan to find NULL values so performance wise there is little or no difference and you give up the functionality provided by using an integer.

Let me direct you to this relevant link:
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Now read this part carefully:
"In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1."

So, IN THE CONTEXT OF THE OP'S QUESTION, you can use EITHER null or zero, because you are saying that if the parent_id is 0, it has no parent, and therefore is "false".

I don't know how to dumb this down anymore for you guys...really...you're killin' me here. Do I have to bust out MS Paint and start drawing pictures or what?
From your own Damn Link
Thus it is entirely possible to insert a zero or empty string into a NOT NULL column, as these are in fact NOT NULL
0 != NULL

I can't speak for why the Author of the article misspoke earlier in the article, but he did

You can dog to wikipedia all you like, but they are right. You want an even better source. Try W3, the guys that set most internet standard!
http://www.w3schools.com/sql/sql_null_values.asp
Note: It is not possible to compare NULL and 0; they are not equivalent.
From the ISO 92 document (anything newer costs money, but if you are willing to pay for it, be my guest).

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
A null value is an implementation-dependent special value that
is distinct from all non-null values of the associated data type.
There is effectively only one null value and that value is a member
of every SQL data type. There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a
null value is desired.
Are the standards committees good enough for you? Or do you still want to link to specific and random tutorials for different DBs to argue your case?

Heck, Why do you think there are so many questions out there on "How to change NULL to zero"? Why would people want to do that if they are the same thing?
 
Last edited:

Cogman

Lifer
Sep 19, 2000
10,284
138
106
Did I say they were equal or did I say there were INTERCHANGEABLE? Yes...NULL and ZERO are interchangeable. Period.
Oh, and this is a blatant lie that is easily disproven.

NULL is 0, broski
BTW in the original context of your post where you use "interchangeable" You were using it to mean equivalent. I'm glad you are finally starting to be able to admit they are not the same thing.

As for the "No arithmetic operators on null" Why on earth would you WANT to do arithmetic operators on ID keys! The sole purpose of an ID key is to link the two tables, the actual number doesn't and shouldn't mean anything. More, the standard usage would be to look up an element, then see if it is linked to another table. NULL will quickly respond "No" while the other IDs will cause the server to attempt to grab information from the other table (Including 0). Ideally, you wouldn't search the elements based on what ID they are linked to, you would search them based on their own ID. And if you did do it based on the ID they are linked to, you would be comparing it to a specific known parent ID, not NULL.

If you wanted all the elements with a non null parent ID, then you can say either where ID > 0 or where ID IS NOT NULL. Either is going to be just as fast as the other.
 
Last edited:

Markbnj

Elite Member <br>Moderator Emeritus
Moderator
Sep 16, 2005
15,682
14
81
www.markbetz.net
You love that word scalar, and yet you don't seem to know what it means. Do you often use keys with non-scalar values? lol I don't really know why you'd even bother to bring it up.

I see strings used as keys, and GUIDs. Are they scalar values? How about composite keys?

Did I say they were equal or did I say there were INTERCHANGEABLE? Yes...NULL and ZERO are interchangeable. Period.

Please exchange 0 for NULL in the following SQL query...

SELECT * FROM dbo.TableA WHERE SomeColumn = 0

If you write...

SELECT * FROM dbo.TableA WHERE SomeColumn = NULL

You will get an error in all the commercial databases I've worked with, because you can't compare the value of a column to NULL. You can't compare a number (0) with the lack of a number (NULL). That's why database designers go to the trouble of implementing IS and IS NOT. And if you have to write...

SELECT * FROM dbo.TableA WHERE SomeColumn IS NULL

... and you still feel that, even though the same boolean operator cannot be applied interchangeably in this case, the concepts of NULL and 0 are still interchangeable, then there is not much more that anyone here is going to be able to say to convince you otherwise.

You seem like a smart guy, although the persona you too often adopt when posting here is the "know-it-all jerk." In this case you're dead wrong, on the theory, and the practice, and just can't seem to let go of it. Defending an untenable position with empty bombast is unlikely to win you much respect.

Oh, and since you appear to respect Wikipedia links...

http://en.wikipedia.org/wiki/Scalar
 
Last edited:

EricMartello

Senior member
Apr 17, 2003
910
0
0
You're all still rambling on but...still wrong.

In the context of this post, null is zero...both can be used to indicate FALSE, which is correct if used for the top level parent_id. It's common practice.

After all that typing you have still failed to show that what I said is incorrect, or more importantly, that it wouldn't work. You see, if you were right, you wouldn't need to link to wikipedia or talk a lot...you just have to say: "No, it won't work because if you use zero as the parent_id it will______" But since you cannot write that statement, you're forced to go off topic.

Null and zero are interchangeable here, and the more you try to say otherwise the more you look like bumbling fools.
 

Cogman

Lifer
Sep 19, 2000
10,284
138
106
You're all still rambling on but...still wrong.

In the context of this post, null is zero...both can be used to indicate FALSE, which is correct if used for the top level parent_id. It's common practice.

After all that typing you have still failed to show that what I said is incorrect, or more importantly, that it wouldn't work. You see, if you were right, you wouldn't need to link to wikipedia or talk a lot...you just have to say: "No, it won't work because if you use zero as the parent_id it will______" But since you cannot write that statement, you're forced to go off topic.

Null and zero are interchangeable here, and the more you try to say otherwise the more you look like bumbling fools.
Did you even READ the ISO and W3 standards which AREN'T the wikipedia?

Our argument isn't over wether it will work or not. It is over saying NULL is 0. Context has NOTHING to do with it, they aren't the same. I've proved my point well enough.
 
Status
Not open for further replies.