Please help with normalisation (1 Viewer)

[Bob]

I have a <person> table, a <house> table and need to register people's tenancy in these houses.

First thought is to create a <tenancy> table with foreign keys to <person> and <house> tables. So far so good.

A tenancy will have a start date and an end date, no problem there. However, a business rule says that a person cannot be a tenant in more than on house at a time so an interdependence happens between records in the <tenancy> table. In this design I must check in the <tenancy> that the business rule isn't broken before a new record can be accepted. This doesn't sound good to me.

What does database theory have to say about that? How do I design this?

Please excuse any lack of proper database terminology.

Thanks.

 

[Mr. B]

If you have described all of the conditions of your data, you do not need the third table because one person can only be linked to one House at a time. You can therefore have a foreign key field in the Person table that allows you to link to the House table for that person. If there are other circumstances and data related to the fact that a person is linked to one house then you may need the many-to-many situation but what you have described is a one-to-many situation.

 

[Bob]

Shockingly simple. Thanks.

 

[Mr. B]

Sometimes things are really simple, and then there are the other times. LOL

Good luck with your project.

 

[dportas]

Another possibility is that a Person may or may not have a tenancy: 1 -> 0..1 rather than 1->1. In that case you can keep the Tenancy table and make the Person foreign key column a candidate key of Tenancy as well (so that no more than one tenancy per person is permitted).