Search code examples
databasems-accessdatabase-designforeign-keysentity-relationship

Access table relationships

Im building a key inventory management database. I have 5 tables and I believe I have to create relationships between them in order to enforce referential integrity.

Here's my question or where im puzzled: In the KEYS table I used the loopup wizard and selected ROOM attribute (so when inserting a new key you see the Room name and not the room ID), however how would I connect the ROOM_ID (from ROOM table) to ROOM (from KEYS ). They aren't the same attribute exactly, would this cause me problems when it comes to keep integrity issues?

enter image description here

This is what my table looks like. If I change the attribute to ROOM_ID (so linking them makes sense) then this table will just have a bunch of id numbers which mean very little.

enter image description here Also when setting relationships, would it be a good idea to always select CASCADE Delete and Update. Is it practical. Thank you.

Solution

  • (1) You want to do it by RoomID. it may not mean anything when you are looking at it in table view, but the users presumably would be using it in a form where you'd do a lookup to show the room name. If the users will be using the table view, however, you could always use a lookup field with a two column dropdown to show the room name, but store the ID.

    (2) Cascade updates and deletes. We can't tell you that. It depends on whether you want cascade updates and deletes to happen in your application. There is no universal answer.