1

A faculty member can take multiple pieces of equipment of campus, and such equipment is specifically assigned to one person.

What would the entities in this data model look like and what would their relationship be?

Cheers

Jack H
  • 13
  • 1
  • 1
    You say:1 Faculty member can "take" multiple piecess....and such equipment is specifically assigned to 1 person...What does this mean? Try to formulate the statement accurately because in modeling, accuracy is very important. – NoChance Mar 01 '16 at 22:29

3 Answers3

1

You have a one-to-many (1:n) relationship between faculty (1) and equipment (n).

So, I'd imagine a Faculty table and an Equipment table would be needed. The primary key of each would be some kind of unique ID (auto-generated like AUTO_INCREMENT, or explicitly given).

Since the 1:n relationship is Faculty:Equipment, you'd have the Faculty.id ("id" being a made-up column name for the primary key) be referenced in your Equipment table.

Therefore, the referential-relationship/bridge/match between these two tables would be Faculty.id and Equipment.faculty_id ("faculty_id" being a made-up column name which represents the corresponding Faculty table's primary key):

Faculty.id = Equipment.faculty_id

jabbascript
  • 345
  • 1
  • 6
  • 13
0

That's a classical 1:n relationship. Have one table for the faculty members, say members with an ID as PK and another table with the assets. The assets have also an ID (which is irrelevant for this) and a FK, say assigned_to which refers to the PK of the members table.

PerlDuck
  • 5,610
  • 3
  • 20
  • 39
  • thanks for clearing that up! would that make it a 1:n identifying or non identifying then? – Jack H Mar 01 '16 at 21:46
  • @JackH According to [this](http://stackoverflow.com/a/762994/5830574) it would be an _identifying_ relationship. – PerlDuck Mar 01 '16 at 22:03
  • Hmm. I re-read that post and now think it's just the other way round. Conclusion: I'm not sure. :-( – PerlDuck Mar 01 '16 at 22:09
  • 1
    "A book belongs to an owner, and an owner can own multiple books. But the book can exist also without the owner and it can change the owner. The relationship between a book and an owner is a non-identifying relationship." Just read this and it leads me to believe it is non identifying. Equipment could exist without an owner – Jack H Mar 01 '16 at 22:43
0

If you ever want to know who check out what equipment over time, your design has to be a little more sophisticated. You need some sort of Equipment Checkout table in between to keep the history of pieces of equipment being checked out and checked back in. When a piece of equipment is being checked out by a faculty member, create the EquipmentCheckout record with pointers to the faculty member and the piece of equipment and the DateOut. Then, link the Equipment record to the EquipmentCheckout record. As long as the CheckoutId field is not null, nobody else can check out that piece of equipment. When the equipment is returned, edit the EquipmentCheckout record with the DateIn, and set the CheckoutId for that piece of equipment to null. Then, you will have a history of who checked out what.

Entities

randyh22
  • 463
  • 4
  • 10