CODEWITHSUNDEEP
mysqlsql
user3259071
user3259071

Reputation:

Would a new table really be needed?

I'm making a sql database for a small company.. Pretty much the other tables don't relate to the question so ill list the two that do...

There is a table:

 NextofKin:
 fname
 lname
 street
 no
 houseno
 city

AND

 Patient:
  ID[pk]
 fname
 lname
houseno
  city

Pretty much would I need a seperate table for street, house and city?

also any idea what i could use as a primary key for NextOfKin?

Upvotes: 0

Views: 61

Answers (2)

Chris Caviness
Chris Caviness

Reputation: 586

Yes, use a pk for next of kin.

Use a joining table between patient and next of kin. Multiple patients could list the same person as next of kin, and while your app may not today require someone to designate multiple people as next of kin, they may change their mind in the future and your application will support it.

Myself, I always use a separate address table. Since usually more than one person lives in a house, and a person can have more than one home, you would again use a joining table.

Upvotes: 1

erik258
erik258

Reputation: 16304

Your questions are starting to get into database normalization.

What you should be doing is never duplicating data between tables unless that data relates the tables, and that data should be indexed. Something like this comes to mind ( there are different ways you might construct it based on business logic )

  • PersonalData: id, fname, lname, address1, address2, city, state, zip
  • Patient: id PK, personal_data_id FK, next_of_kin_id FK

Granted most of the tables already exist so this may be impossible. But to answer your question directly, since the database is not normalized already, there's no good place to put further address records ( don't want them under Patient right? ) and so you're stuck duplicating the data. Even so, there has to be some relationship between Patient and NextOfKin, so either Patient holds a reference to NextOfKin, or NextOfKin hods reference to Patient. Either way, you might consider using a foreign key between them to enforce, and explicitly state, this relationship.

Upvotes: 1

Related Questions