In a One to One relationship should i drop one of the table's id column?

Question

I have the following 2 tables in MySQL: Customer(Id, Firstname, Lastname...) Bonus(Id, CustomerId, Value, ...)

The relation is One-To-One, every customer has only one bonus.(the CustomerId is unique in the Bonus Table)

Q: Should I drop the Id column of the Bonus table? (I want to know why or why not)

Answer 1

if it's ono-to-one, why is there any extra table? you could instead put "bonusvalue" into your customer table.

(else: yes, you can drop the id of the bonus-table, the customer-id is the primary key and the "id" is completely redundant)

Answer 2

I would remove the Bonus.Id coulmn and make Bonus.CustomerId the PK. Doing this will remove the need to have a unique constraint on the Bonus.CustomerId column, since it will now be a PK. Anyone looking at the table will see the one-to-one more clearly without the Bonus.Id coulmn. You won't need an index on Bonus.CustomerId, the PK index will be all you need, so less disk space and memory cache wasted. Also, if you ever need a FK to the Bonus table, you you would use the CustomerId value (the new PK), which can be used to get back to Customer or Bonus tables, not just Bonus.

Answer 3

I assume it isn't actually a true one-to-one because you could presumably have a Customer without a bonus row. SQL-style foreign key constraints are always optional on the referencing side of any relationship.

I agree the Bonus.Id column appears to be completely redundant.