当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Is it OK to use 2 foreign keys in a table, one of which must be null?

问题描述:

If I have a table where each row is a customer booking, is it OK to have 2 foreign keys in this table: one which will be null and the other referencing the primary key approriate. The reason I have two tables with the foreign key constraint applied to this table is that a booking can either be a reservation of a package of services (service_package) or a single service (service).

My guess is that this is bad design because it becomes possible to register two kinds of bookings in the same booking row, unless enforcing restrictions by using functions or stored procedures.

Is this solution OK or are there better ways to do this, like creating a more general table for the services provided?

网友答案:

This is a reasonable approach. The joins will fail when the the values are NULL for a particular column.

In MySQL, you have to enforce this with a trigger. Other databases have the concept of a "check constraint" where you can enforce that exactly one of the two value is populated.

If you have more columns, you might be tempted to switch to a "type" column along with an "id". This requires that all the ids have the same type. It also requires a trigger to ensure that the columns are populated correctly. And, it can lead to errors when doing joins.

Of the two alternatives, I prefer your method for just two ids.

网友答案:

Although having nullable FKs is not wrong, from a performance point of view is not always advisable. As correctly mentioned in this post:

B-tree indexes are most effective for high-cardinality data (i.e. columns with many possible values, where the data in the column is unique or almost unique). If you will be having many NULL values (or any other repeated value), the query optimizer might choose not to use the index to filter the records for your result set, since it would be faster to do a table scan.

I would opt for having type/id columns with a compound index (id,type).

分享给朋友:
您可能感兴趣的文章:
随机阅读: