CODEWITHSUNDEEP
sql-serverdatabase-design
001
001

Reputation: 65205

Database design 1 to 1 relationship

I design my database incorrectly, should I fix this while its in development?

"user" table is suppose to have a 1.1 relationship with "userprofile" table

however the actual design the "user" table has a 1.* relationship with "userprofile" table.

Everything works! but should it be fixed anyways?

Upvotes: 3

Views: 548

Answers (5)

HLGEM
HLGEM

Reputation: 96640

Yes, fix this with a unique index on the FK field. The reason why you need to fix it now is that you can't control how badly people are going to insert data over time when the database is not set up correctly with controls that do not allow the behavior you do not want.

The first time you havea a duplicated record inserted into the child table, you might break a lot of code. With no unique index, the chances of a second record getting inserted can be quite high. You can say, you'll control this at the application level but that is usaully a poor choice as there is no guaranteee that other applications, bulk inserts etc aren't gong to happen that circumvent the application. Putting things right as soon as you can in a database design is critical. It becomes really hard to fix a poor design when there are a lot of records in the database.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453910

If it's a 1:1 relationship and you often are bringing back records from "user" table and "userprofile" together then you might consider just merging them into one table.

Upvotes: 2

001
001

Reputation: 65205

@pranay

User Table 
   Userid(p)
   UserName
   othercol..

UserProfile 
   id(p)
   UserId(f) - and unique
   othercol..

Is that normally how you do it(above)? or do you do this(below)?

User Table 
   Userid(p)
   UserName
   othercol..

UserProfile 
   id(p) <--- userid
   othercol..

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176956

Do one thing 

User Table 
   Userid(p)
   UserName
   othercol..

UserProfile 
   id(p)
   UserId(f) - and unique
   othercol..

hope this way you can easily fix the isse

Upvotes: 3

Justin
Justin

Reputation: 4116

Make the user_id in the user_profile table unique and its fixed.

Upvotes: 2

Related Questions