CODEWITHSUNDEEP
mysqldatabase-designforeign-keys
alex
alex

Reputation: 103

foreign keys between tables: Buildings and Activities have Addresses

I have 3 entities:

I don't know how to put foreign keys between tables.

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

But I want one address table.

Consider 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?

Upvotes: -1

Views: 167

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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

Upvotes: 0

home
home

Reputation: 12538

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)?

Upvotes: 0

Marco
Marco

Reputation: 57573

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!!

Upvotes: 1

Related Questions