Database design 1 to 1 relationship

Question

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?

Answer 1

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

Answer 2

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

Answer 3

@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..

Answer 4

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.

Answer 5

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.