using dummy row with NOT NULL to solve DEFAULT NULL

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 do开发者_高级运维ing: 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.

Is this a good design or any other work arounds?

ISNULL or COALESCE and LEFT JOIN

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.

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.

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

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:

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

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:

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.