Help understand DB Schema/Design

Question

Hello i am facing the following...

Two DB tables (mysql myisam tables - no foreign key constraints) 'MASTER' and 'CHILD'. Rows in 'CHILD' db table reference a single row (1:M) in the 'MASTER' table using the parent_id *column*.

The problem is that the parent_id column has 2 "types" of values:

• A NON-ZERO value referencing a row in the 'MASTER' table
• A ZERO value meaning that the row is not referencing the 'MASTER' table. In this case rows with ZERO values in the parent_id column 'actually mean' something else => represent different type of data (stored in the same table? i.e storing apples and oranges in the same table when the two might be best to be stored in different table)

I am confused. Is this simply a bad database design? Is it common practice to differentiate 'what rows in a DB table represent' using different values in a column which is used to reference 'parent rows' in another table.

If you are not confused and can help, please do :)

Answer 1

When you are discussing what rows in a table "actually mean", you are crossing the line from logical data modeling into conceptual data modeling, the semantics of the data. In general, storing rows of two different types with regard to actual meaning in one table is a bad idea.

It's a bad idea from the point of view of speed, because most of your queries are going to involve extra disk io to weed out rows that don't pertain to the current query.

It's a bad idea from the point of view of producing bug free code, because it you forget to weed out rows that don't pertain, you'll get wrong results.

It's a bad idea from the point of view of flexibility, because data that is inappropriately bound together is harder to alter when the requirments change.

It's bad design.

Having said that, there are optional FK fields in well designed databases. In such cases, the rows that opt out of the relationship contain NULL, not zero in the cell. However, even in such cases, the semantics of the remaining columns isn't affected by whether the relationship is present or absent.

Answer 2

I don't have enough information to say whether this is a bad design or not.

It sounds to me like a standard one-to-many relationship.

You are distressed about the two different values in the CHILD column that references the MASTER. It sounds like the usual way of representing a nullable foreign key to me. It says that you can have a CHILD row that does not belong to any MASTER row. Any example would be a database for a school that assigns one or more STUDENT rows to a SCHOOL row.

The reference from the STUDENT table can be null; after all, we don't kill/delete a STUDENT who happens to be unassigned to a school, do we?

I'd ask why this isn't being done with null values. Are you sure that you're not seeing the way your database happens to represent null? If someone invented their own way of doing it, and bypassed null out of willfulness or ignorance, then call it a bad design.

Both tables must have primary keys. It's best if the MASTER column referenced by CHILD is the primary key of the MASTER table. If not, call it a bad design.

UPDATE: I can think of one historical reason that might explain it.

Older versions of MySQL did not enforce referential integrity until the ISAM engine came along. Your schema might have been developed prior to ISAM, so developers decided that they had to manage referential integrity on their own. If you've updated to ISAM, perhaps the schema wasn't ported along with it.

Please ignore this idea if my assumption about history is incorrect.

Answer 3

This is very bad design. What you want is a third, linking, or join table the has two columns: the id of your so-called child table and the id in the master table. There will be an entry in this table for every row in your child table that currently has a non-zero parent-id.

Update: maybe 'very bad' is an over statement, as you could use a nullable foreign key as others have stated, but it should depend on the frequency of the nulls. If most rows have a non null link to master than go with the nullable FK, otherwise use a linking table.

Answer 4

You would normally do that with a nullable foreign key constraint. The idea of using a single column for two different things is generally a bad idea. The nullable foreign key constraint forces non-null values to have referential integrity but still allow orphans in the child table.

The problem with not having the foreign key is that someone can insert values into the child table that have no corresponding row in the master table (or delete the referenced row in the master table later on).

That's not a good design - the database should be responsible for its own integrity.