Search code examples
database-designerdconceptual

Basic Database design question - 1 attribute three Entities

Please can someone clarify something which I should really know by now?

If I have Three Entitys Organisation, Director and Company secretary and each have the attribute primary address.

If I were modelling a conceptual ERD should I have the attribute ‘primary address’ separately in each entity or should I model a fourth entity Address, add primary address and have them all PK > FK to that?

Have since found this link Is this a good way to model address information in a relational database? which pretty much covers everything I need to know.

Solution

  • I would do something like this, assuming primary address refers to the Organization location:

    Table: Organization

    Org_Key {PK},
    Org_Name,
    Primary Address fields,
    Any other fields

    Table: Personnel

    Person_Key {PK},
    Org_Key {FK},
    Position {Directory, secretary, etc},
    Any other fields

    In my opinion, there is really no need to separate those who are "Directors" from those who are "secretaries" because they are both employees of a specific Organization.

    Now, if by primary address you mean a personal address (i.e., an individuals home address) then I would add additional fields to the Personnel table.