Scenario description
I have this person
table as super parent (or supertype),
id
firstname
lastname
email
telephone
...
...
and user
table as a child (or subtype)
id
person_id (FK)
password
username
screenname
...
...
They must be in a one-to-one (1:1) relationship, because an user cannot be repeated twice, and so a particular email value in a given person row must not be repeated twice.
Then I have this message
table which stores messages from anyone,
id
firstname
lastname
email
telephone
subject
content
...
...
but you can see that firstname
, lastname
, email
, telephone
are duplicated in the message
table.
So, I am thinking to refer it to person
table like this below,
id
person_id
subject
content
...
but then it does not seem right, as a person with the same email, name, etc. can send a message to me as many times as they want, so the details he/she provides can be repeated.
Questions
-
So, should I make
message
as a child ofperson
the parent or they should be separate entities? -
Or, are there any better suggestions to solve this problem?
Best Answer
Fine (just changed the name of the PK column):
If the relationship is 1:1 (assuming that
person
is the supertpe anduser
is the subtype, you can handle this with having the same column both as Primary Key and Foreign Key toperson
:Add a
UNIQUE
constraint in theperson.email
column.Fine (adjusted to previous changes). But you also need to store who sends a message and who is the receiver: