Blank field in primary key table or blank field in foreign key table?

[starson79]

Hi

Something that I'm not sure how to word so apologies if already appeared in this forum.

Example:

Table <CustomerTitle>
PK: CustomerTitleID
CustomerTitleName

CustomerTitleName = Miss, Mrs, Mr etc....

Is there any benefit in inserting a record for unknown records? e,g,
CustomerTitleID = 1
CustomerTitleName = <blank>

Table: <Customer>
FK: CustomerTitleID

OR

Allow the foreign key field to be empty in those cases where CustomerTitle is unknown?



Thanks

[jzwp11]

I would probably just leave the foreign key field in the customer table blank if the title is unknown. You will have to adjust your relationship between the two tables in the relationship window so that you return all records from the customer table. You would want to select this option for the join type when you build the relationship:

Include ALL records from 'Customer' and only those records from 'CustomerTitle' where the joined fields are equal.

[starson79]

After giving this some thought I just wonder if it's best to specify a unique record for blank entries.

When a SQL query is run it creates a sequence of virtual tables which hold the temporary results per SQL command performed. An extra step (virtual table) is created for an outer join.

If I specify a unique record for blank entries then I won't need to specify outer joins in my queries - meaning the extra step is not required - meaning the overall performance of the database should be better because it's not performing outer joins for nearly all queries.

Any thoughts? This means I'm going to have to change my database design again. Aargh. (I'm not really sick - I just felt like putting that silly face in there)

[jzwp11]

After giving this some thought I just wonder if it's best to specify a unique record for blank entries.

I don't know what is the most efficient way in terms of database performance, but it is not unusual to have an outer join. I guess it is your call. The only problem I see is if you end up having multiple records in the title table that have a null value for the title field, then there would be confusion.

[starson79]

I've changed the database tables to disallow blank foreign key fields - and I've specified a unique record in most tables for 'Unspecified'. This means there's less need for outer joins.
thanks