开发者

one-to-one relationship in database

Suppose that I have a table users, with a PK id_user.

I also have an identifying relationship between the table users and th开发者_JS百科e table employer. I can use the PK id_user as foreign key in the table employer and also as PK (with unique constrain) in this table?

In this case, the employer only have a worker and a worker only have an employer.


Short answer: Yes. When a foreign key is also the primary key of the child table it forces it into a one-to-one.

Longer answer: in my experience every 1-to-1 I've every made has been expanded later into a 1-to-many or many-to-many, as the users' requirements are better understood. Example: You end up needing a history of employers, so suddenly you have a many-to-many from persons to employers with effective dates.

After this happened a few times I made it a point to dig into the reasons why a 1-to-1 seemed to make sense, and always found it did not. So much so that I made a rule of thumb for myself to avoid 1-to-1 tables, as they usually indicate an incomplete understanding of requirements.


Primary keys are special unique keys. In this case I would not link worker in the employers table as typically an employer has more then one worker. If you're absolutely sure it is a one-one relation, I do not see why you use 2 tables, instead of one. Meaning, add the employer fields to the worker table. A real one-one relation is for example a phone number or email address and typically they are stored with the owner, not in a seperate table.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜