Nullable Key Field

[Felgar]

Ok, my situation. I have a record that has a name, type, and third description field. Now, normally name and type will uniquely identify a record. So basically, for each name, I can have a "type a", "type b", "type c", "type d" record. But on "type b" records, the description field comes into play. The third field is a sub-type and is one of a few choices.

Normally I would just expand the key from name and type to name, type, and description. But the description only applies to the "type b" records, so it would be null otherwise, and thus that will prevent me from making the description field part of my primary key.

Aside from the 3 important fields, there are about 15 others that hold the exact same data for all records. Does anyone have some ideas on what would be an appropriate way to handle this from a database design perspective?

One way would be to set the description to something like "NO_DESC" for all except the "type b" records, but I'm not too keen on doing that...

 

[sem]

I think that the better way is to denormalize this table (split it into a number of more clear ones)

 

[CRoberts]

There is quite a bit of discussion on primary keys I have read over the years over "nature" and "artificial" keys. My feeling is that a business rules should never be used as primary key. I always used a generated key. There are two reasons for this:

a) If your business rule change, your primary key and thus your entire database is hosed.

b) Simplicity. If you ever had to create some very complex views or SELECT statements, having a compound primary keys can be a real pain. Consider a simple view (no subquery or in-line views or the like) with only 3 levels of parent-child relations (which are one to many) where the primary key expands from 3 to 4 and then 5 columns. Consider the number of WHERE clause references just to join the 3 tables.

Use Sequence Generators for your Primary keys. Then, enforce any Unique keys constraints in a PL/SQL program or triggers. Don't put your database at risk where a business rule change can affect the database integrity.

 

[DaPi]

CRoberts - spot on!

After more than 20 years wrestling with db's of various types I agree 100%. Every time I broke the rule of having "meaningless" PK's I regretted it. e.g. I made the exception with country codes - then Finland went from SF to FIN!

 

[DaPi]

While I'm on my hobbey-horse: beware of meaningless keys supplied by someone else! Employee number is a good one - looks fine as a PK, until the HR people want it changed when someone moves to a different branch/department!

 

[Felgar]

Thanks for the input everyone. I can definately see your point CRoberts. However, I'm not sure that I completely agree. The problem that I have with generated PKs, is that then I truly know nothing about the data.

I can't count the number of times that I've been told, "This set of data here is guaranteed to be unique" and then naturally, it isn't because it's not enforced in the database. So now I'm stuck dealing with the company trying to convince them that their data is wrong and they need to fix it. But if the data is the PK, or at least a unique index, then at least I have something to build from.

If I'm to understand you though CRoberts, you would implement the PK as a generated number, and then perhaps program a trigger or use indexes to ensure that the business rules are enforced? And then when they change all that's required is to redo that trigger... Interesting approach, I will definately keep it in mind.

Thanks again all.

 

[sem]

Nothing prevents you from using other unique keys. Though, when you change the "meaningfull" key value in parent record, you may be sure that all the child records if any still corespond to it without need to implement complex "on update cascade" rules.