Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this person table as super parent,

id
firstname
lastname
email
telephone
...
...

and user table as a child

id
person_id (FK)
password
username
screenname
...
... 

They must be 1:1 relationship, because an user cannot be repeated twice. and so the email in the 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 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 message to me as many times as they want. so the details he/she provides can be repeated.

so should I make message as a child of person the parent or they should be separate entities?

or any better suggestions to solve this problem.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

I have a person table.

Fine (just changed the name of the PK column):

person
------
person_id    PK
firstname
lastname
email
telephone
...
...

and a user table as a child. They must be 1:1 relationship, because an user cannot be repeated twice.

If the relationship is 1:1 (assuming that person is the supertpe and user is the subtype, you can handle this with having the same column both as Primary Key and Foreign Key to person:

user
----
person_id  PK  FK to person(person_id)
password
username
screenname
...
... 

and so the email in the person row must not be repeated twice.

Add a UNIQUE constraint in the person.email column.

Then I have this message table which stores messages from anyone, but you can see that firstname,lastname, email,telephone are duplicated in message table. so I am thinking to refer it to person table like this below.

Fine (adjusted to previous changes). But you also need to store who sends a message and who is the receiver:

message
-------
message_id   PK
sender_id    FK  to user(person_id)         --- or to person(person_id)
receiver_id  FK  to user(person_id)         --- that depends on your requirements
subject
content
...
share|improve this answer
    
Thanks for the answer and suggestion, ypercube! :-) –  lauthiamkok Feb 26 '12 at 14:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.