Danny Stolle <[EMAIL PROTECTED]> wrote on 01/23/2006 01:34:01 PM: > Hi, > > I am often running into this type of design, but would it be a good > design. Normally you design tables related to each other and having e.g.
> parameter tables like a country-table holding country names with their > country-id's; and you will find these country-ids back in other tables, > which have the foreign key country_id related to the primary key > country_id. (voila). > > Would it still be a good design if you would except NULL values in a > related field which hold the foreign key; because it could be possible > that nobody would enter or select a country. The trouble would be i > guess, consistency; because you now have a value (NULL) which is not > present in the country table. > > The country_id is an example, but you can guess there would be more > examples you can think of. > > If I am not clear enough please let me know; and I can be more specific. > > Danny > IMHO, that is a perfectly workable solution to certain data situations. In your example, the value must either be NULL or one of the country_id values in your country table. Sounds reasonable if the data fits the rules by which your application must operate. It still works with InnoDB and Foreign Key constraints. I know because in one of my databases, I have a table that holds a reference to a "state_id" (FK-ed to the another table of "states"). However my "states" table only contains entries fo the states in the US and Mexico, and the provinces of Canada. For any entry where the "state" is unknown (like a UK address) I have a NULL value and everything is clicking right along. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
