MySQL database basic design

I have 3 entities:

• buildings
• activities
• addres开发者_如何转开发ses

And I don't know how to foreign key the relationships between tables.

Buildings are located at addresses. Activities are performed at addresses (one address at a time).

But I just want one address table.

Suppose the next attributes:

Buildings(id,phone,email,image,comments) <- should I put address_id ? Activities(id,description) <- should I put address_id?

Addresses(id,street,city,state,postcode) <- or should I put center_id and activity_id here?

Thank you in advance!

---

You should use address_id both in buildings and activities tables.
Address is unique, while many buildings and many activities can be located to the same address!!

---

Your question implies that multiple buildings can be located at the same address - is this what you want? If so, just normalize it accordingly:

The Address is your 'root entity':

ADDRESS(address_id,street,city,state,postcode)

A Building can be located at exactly one Address, so include a reference to Address, a foreign key:

BUILDING(building_id,phone,email,image,comments,address_id)

An activity is performed at exactly one address, references to Address by foreign key:

ACTIVITY_ID(activity_id,description,address_id)

Maybe you should think about whether:

• a street number is missing in the address?
• should having more than one building at a given address be possible?
• more than one address for a given building is possible (yes, I've seen this)?
• a separate ADDRESS table is really necessary (see above questions)?

---

Alex, you should have the IDs in both tables, as you're saying in your question. There is no need to have them in separate tables as actually the address of a building will be where an activity will be performed, right?

If you are worried about two buildings having the same location then add a uniq index in the address_id column of the buildings table.

Moving a bit forward. Can you have an address without a building? If that is the case, then you could even add the address data (columns) to the buildings table. Because it would be a one-to-one relationship and no other entity would need to use the address table but the buildings one. That way you would get rid of the addresses table