CODEWITHSUNDEEP
mysqldatabase-design
Chella
Chella

Reputation: 1562

which of the way I have to use foreign key this situation??

Hi I have a little confusion..! 

address_details(e_address_id(pk),hno,street....)
emp_details(eid(pk),ename,...,e_address_id(fk))

or

emp_details(eid(pk),ename,...,)
address_details(e_address_id(pk),eid(fk)hno,street....)

either way it works..But i am unable to decide which I need to choose, and let me know wchich one better.. I am a newbie for DB Design. thanks in adv

Upvotes: 1

Views: 50

Answers (2)

Hiren Desai
Hiren Desai

Reputation: 1091

You need ti understand Multiplicity concept or relationship concept in Relational Database.

address_details(e_address_id(pk),hno,street....)
emp_details(eid(pk),ename,...,e_address_id(fk))

This Structure will called (emp entity1 and Address entity2) 1-many relationship from Employee to address. Which means that Multiple Employee can live on same address. But at the Same time One employee can't live at multiple location
So EmpA lives at Add-A
EmpB lives Add-B
Empc lives Add-A (where EmpA also lives)
In following structure EmpA can't live at two addresses.

emp_details(eid(pk),ename,...,)
address_details(e_address_id(pk),eid(fk)hno,street....)

The above structure is Many-one Relationship.
Where one person can live at multiple address but two person can't live at single address. hope this explains a lot.

To understand more read multiplicity in Relational databases

Upvotes: 0

Joel Brown
Joel Brown

Reputation: 14388

The first design is better, but it may not be good enough.

The second design requires that should two employees who share the same address, the address details would have to be stored reduntantly.

The first design allows for multiple employees to share an address, which might be necessary. The first design, however only allows an employee to have one address. If that is all your system needs then the first design is fine.

However, if you might need to allow employees to have multiple addresses and addresses to be shared by multiple employees, then you would have to consider removing the foreign keys from either table and moving them to a new intersection table:

employee_address(e_id(fk), e_address_id(fk))

The design you choose depends on your business rules and what is reasonable to expect in reality.

Upvotes: 1

Related Questions