1

I have three tables in my PostgreSQL database:

User - Contains a username and password
MaleProfile - Contains information related to each male user
FemaleProfile - Contains information related to each female user

Initially, instead of having separate MaleProfile and FemaleProfile tables, I had a single Profile table. In that situation, I would have had a one-to-one relationship between the User table and the Profile table. But I've since decided that I really need separate profile tables for men versus women. In this new situation, each record in the User table must map to one and only one record in either the MaleProfile table or the FemaleProfile table (but not both). From the other direction, each record in the MaleProfile table maps to one and only one record in the User table. The same holds true for each FemaleProfile record.

Strictly speaking, the relationship between the User table and each of the profile tables is one to zero-or-one. But are these relationships essentially just one-to-many relationships in the sense that "many" in this case means just zero or one (but not more than one)? If so, would I express them as you would any one-to-many relationship by creating a foreign key column in the MaleProfile table and in the FemaleProfile table, each of which points to the PK column in the User table? Would I need to add any additional constraints to the profile tables to maintain referential integrity?

Thank you.

Jim
  • 13,430
  • 26
  • 104
  • 155
  • Why did you have to seperate the tables? – Elias Aug 09 '13 at 14:33
  • Because they were different enough that there would have been too many fields that could be blank. This would have required me to create some gnarly business logic to ensure that Django validates the associated models correctly. It seems cleaner and simpler to split them up if I can implement referential integrity via a one-to-many relationship between each table and the User table. – Jim Aug 09 '13 at 14:38
  • You might be better off having a boolean flag in the User table, along with a foreign key that points to either the MaleProfile or FemaleProfile primary key. Either this way or the way you described, your application will have to maintain referential integrity. The database can't maintain referential integrity by itself. – Gilbert Le Blanc Aug 09 '13 at 14:44
  • Could use a junction table as well `ID|MaleProfileID|FemaleProfileID` – Elias Aug 09 '13 at 15:00

2 Answers2

1

Just make sure the referencing column in your male/female tables has a uniqueness constraint on it as well as a foreign key constraint. This is a 1-to-1/0 relationship, not 1-to-many.

CREATE TABLE MaleProfile
 (UserId INT NOT NULL PRIMARY KEY
 REFERENCES "User" (UserId));
nvogel
  • 24,981
  • 1
  • 44
  • 82
  • One follow-up question. You said I should create a unique constraint as well as a FK constraint. I understand that REFERENCES creates a FK constraint. Does it also satisfy your uniqueness requirement by virtue of the fact that UserId would be referencing the PK column in User that is, itself, guaranteed to be unique due to the fact that it's the PK in the User table? Thanks. – Jim Aug 14 '13 at 17:05
  • A second follow-up. In Django, the closest I can come to implementing your answer is to specify in my MaleProfile model that UserId should be a OneToOneField (My only choices are OneToOne, ForeignKey, and ManyToManyField). This translates to "male_profile_user_id_key FOREIGN KEY (user_id) REFERENCES auth_user(id)" in PostgreSQL. Will this be OK? It means that when I create a MaleProfile record, the UserID must match the id field in some row of the auth_user (i.e. the User) table. – Jim Aug 14 '13 at 17:32
  • In answer to your first question: No. The uniqueness requirement I was referring to is the constraint in MaleProfile. UserId must be unique in the MaleProfile to achieve the 1-1 relationship. In your second follow-up question you haven't mentioned the uniqueness constraint on MaleProfile. UserId ought to be unique as well as being a foreign key. – nvogel Aug 14 '13 at 20:13
0

I believe that you should put the employeeID number in the profile tables and enforcing a unique constraint in those tables. Although I can't really think of how to keep someone from having both a male and female entry, I believe that this way is the way to go.

Check out https://stackoverflow.com/a/669015/1504882 to see a similar situation to yours, but instead with different types of employees.

Community
  • 1
  • 1
Elias
  • 2,602
  • 5
  • 28
  • 57