Search code examples
sqlsql-serverentity-relationship

Entity-relationship model

I am creating a simple entity-relationship model. Here I have one or more animals that can get one or more services. ER-model My question is which attributes's the connection type "get" going to have. I was thinking that it will get the unique attributes of animals and svervice that is "animal-ID" and "service-ID", and that will be foreign keys in "get".

Solution

  • Typical many-to-many relationship looks like table with two columns referencing two entities. In your case it's (actual syntax depends on dbms):

    CREATE TABLE Get (
  animal_id INT NOT NULL,
  service_id INT NOT NULL,
  FOREIGN KEY animal_id REFERENCES animal (animal_id),
  FOREIGN KEY service_id REFERENCES service (service_id)
)

    Sometimes it makes sense to extend linking table by some link properties. Like:

    CREATE TABLE Get (
  animal_id INT NOT NULL,
  service_id INT NOT NULL,
  service_paid BOOLEAN,
  provided_date DATE,
  FOREIGN KEY animal_id REFERENCES animal (animal_id),
  FOREIGN KEY service_id REFERENCES service (service_id)
)

    But in general it's just two foreign keys.