0

I know having DEFAULT NULLS is not a good practice but I have many optional lookup values which are FK in the system so to solve this issue here is what i am doing: I use NOT NULL for every FK / lookup colunms. I have the first row in every lookup table which is PK id = 1 as a dummy row with just "none" in all the columns. This way I can use NOT NULL in my schema and if needed reference to the none row values PK =1 for FKs which do not have any lookup value.

Is this a good design or any other work arounds?

EDIT: I have:
Neighborhood table
Postal table.

Every neighborhood has a city, so the FK can be NOT NULL. But not every postal code belongs to a neighborhood. Some do, some don't depending on the country. So if i use NOT NULL for the FK between postal and neighborhood then I will be screwed as there has to be some value entered. So what i am doing in essence is: have a row in every table to be a dummy row just to link the FKs.

This way row one in neighborhood table will be:
n_id = 1
name =none
etc...

In postal table I can have:
postal_code = 3456A3
FK (city) = Moscow
FK (neighborhood_id)=1 as a NOT NULL.

If I don't have a dummy row in the neighborhood lookup table then I have to declare FK (neighborhood_id) as a Default null column and store blanks in the table. This is an example but there is a huge number of values which will have blanks then in many tables.

Tony38
  • 123
  • 4
  • 1
    Instead of telling us how you implemented your schema, you should tell us what you were trying to accomplish. – John Saunders Dec 24 '10 at 21:53
  • goal is to not have any "default null" in my schema. But ofcourse many times values are optional and i can't make those column a NOT NULL. So I am looking for a work around. The way above it the only way i can think of, and want to see if there are better work arounds or maybe if it is ok to leave columns as default null then which would be almost 50% of the FKs then. – Tony38 Dec 24 '10 at 21:55
  • 1
    "what you were trying to accomplish" = "what your schema is about" or "what is the bigger problem you're solving". – 9000 Dec 24 '10 at 22:10
  • Your effectively just reassigning nulls a new (and more obscure) value, so why is it good? http://stackoverflow.com/questions/925203/any-example-of-a-necessary-nullable-foreign-key – nate c Dec 24 '10 at 22:17
  • Also, why do you feel that null values are a problem? – John Saunders Dec 24 '10 at 22:18
  • I dont feel it is a problem, just heard around that having too many nulls is not good. And there is a almost 50% chance that null values will exist. I cant super normalize the tables because this is a social network and need to keep joins low to keep performance at par. So i am doing just enough to separate tables by data usage. – Tony38 Dec 24 '10 at 22:31

4 Answers4

0

Is this a good design or any other work arounds?

ISNULL or COALESCE and LEFT JOIN

ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
0

Often "None" is an option like any other in a list of options. It may be totally reasonable to have a special row for it; it simplifies things. It may be especially practical if you link other information to options, e.g. a human-readable name.

9000
  • 39,899
  • 9
  • 66
  • 104
0

This seems like a simple case of premature optimization in a database:

alt text

If your schema is something like this, then I don't see a problem. Some postal codes are in a neighborhood, some aren't. That's a good case for a nullable column.

The advice about avoiding nulls is about avoiding information that does not belong in the table. For instance, if you had another five columns which only pertained to postalcodes which were in a neighborhood, then those columns would be null for postal codes which were not in a neighborhood. This would be a good reason to have a second, parallel table for postalcodes which were in a neighborhood, which could contain these other five columns.

More importantly, if performance is a concern, then the solution is to try it both ways, test the performance, and see which performs best. This performance consideration would then compete with the simplicity and readability of the design, and performance might win.

An example to illustrate the issue. I started with an Object-Role Modeling model, the same that I used to produce the earlier ER diagram. However, I created a subtype of PostalCode and added two more mandatory roles to the subtype:

ORM Model

This can produce an ER model very similar to the first:

ER Model Adsorbed

But this model fails to show that there are columns which are mandatory whenever the PostalCode is a NeighborhoodPostalCode. The following model does show that:

ER Model Separated

I would say that if you have a set of optional columns which are mandatory under certain circumstances, then you should create a "subtype" which always has those columns NOT NULL. However, if you simply have random columns which may randomly be not null, then keep them as NULL columns in the main table.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
  • No, not talking about UI. Only in the database. I added more details to the question with example of what i am trying. – Tony38 Dec 24 '10 at 22:33
  • Yes the schema is correct. And there are much more columns in the table Like postal code has latitude, longitude, mapping to a country table also. So in essence you are saying it ok to have nulls in the table even if it is half of the values in a column? – Tony38 Dec 25 '10 at 02:36
  • It depends. It's perfectly reasonable, in general, for some entity to have values which will sometimes be unknown. The problem comes when some subset of those values are mandatory sometimes but not others. I'll try an example. – John Saunders Dec 25 '10 at 03:03
0

You can always use left joins to join postal codes that may not exists.

select * from from table_a 
  left join table_b    
  on table_a.postalcode_id = table_b.postalcode_id

will select rows whether or not the postalcode_id is null or not. When you use magic numbers to designate nulls then queries become less readable.

clear:

select count(*) from table_a where postalcode_id is null;

Not so clear:

 select count(*) from table_a where postalcode_id = 1;

Using nulls makes your queries explicitly handle null cases, but it also self-documents your intentions that nulls are being handled.

nate c
  • 8,802
  • 2
  • 27
  • 28