I am wondering which is a better way of designing my data base.

Currently designing for Hospitals that advertise positions which require specific skills (e.g., nursing, administrative, etc.). Candidates may be invited to interviews for the positions.

For one of the requirments I have to include information of the hospital details such as: hospital identifier, hospital name, address, and telephone number. I also have to include details on candidate, position and interviews in other tables.

My question is, would it be better to include the address of the hospital in the column of the hospital table or is it better to create a separate table called address for the address of the hospital and create a one to one relationship with the hospital table ?

I tried putting the address in the column of hospital anyway but i'm wondering which is better practice and if it makes more sense to put address's in its separate table.

CodePudding user response：

You really need to ask yourself if you need the relationship between a hospital and it's address? Are there going to be more than one hospital at an address? What does the relationship serve?

Generally, having a relationship simplifies updating things. If I have 100 things all at the same address, and I need to update the address, then it's easier to have that address isolated as a single row in a related table.

But, if there's only ever one thing at one address.... well... that kind of answers the question for you.

Best practices sometimes apply, but I've found more often than not if you just ask "Why" and think about it critically, you'll come up with the right way for yourself.

CodePudding user response：

Database normalization is a fickly thing.. At school the options are limited since there are tutors around proclaiming it should be in a different table since it's an Address and therefor could be used multiple times.

However, the world does not work that way.

Does - in this case - your hospital have multiple addresses, do they change location a lot? Most likely answer is no; therefor: when in a real-world situation choose the most likely option and just add the Address to the table.

Be wary for extra tables, since JOIN operations do (in real world situations) have a penalty. That's not saying you should just forget everything about Normalization, but you should be thinking about real-world-implications of your datamodel.

It has no real use to add each address to a separate Address table if each Address is just as unique as the foreign key they're bound to..