views:

62

answers:

3

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)

A: 

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)

oezi
a one-to-one relationship is good when most rows of the main table do not need the a row at all in the second table. For example, you have a main "Person" table and then a one-to-one table "DoctorInfo" and another one-to-one "StudentInfo", etc.. you wouldn't want to include all the DoctorInfo or StudentInfo columns in "Person"? not me, I'd split them out into one-to-one tables.
KM
You also split out tables into a one-to-one when the table row length is too large for efficient access. Or if the data will only rarely be queried and the main table will be frequently queried. Or of you want to limit the visibility of the data.
HLGEM
It is redundant, but i don't agree with putting everything in one table, what about address, even if it's a one to one. I like to make my tables like i do my objects, need to have only. As many classes/tables as i need.
Vlad
+3  A: 

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.

KM
Thank you for the feedback, i usually add an id column to all the tables i create and i don't think about it too much, but i started to argue about it with a colleague of mine who suggested i drop it. I wanted to get more opinions on the matter before i do it.
Vlad
A: 

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.

dportas
You are right, not all customers have a bonus.
Vlad