Access table relationships

Question

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?

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.

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

Answer 1

Firstly it is my opinion that you should follow some common naming/style conventions for your tables and columns. For example it is quite hard to distinguish if ROOM_ID is referencing KEY_ID or vise versa.

It would be a lot easier to follow if If the tables and columns were:
ROOM.id (PK), ROOM.room.
KEY.id (PK), KEY.room_id (FK), KEY.drawer, KEY.notes

Assuming that a room can have more than one key, there will be unique KEY.id but the same KEY.room_id.

To answer the question about if the columns are not the same attribute, setting up a relationship between two tables involves the ability to reference each other coherently so I believe it would be a problem. If you need to set more information then you can by adding another column.

You can read more about conventions at this website : Here

Regarding the CASCADE Delete and Update, it depends on the way you are intending to use your database. For example if you were to Delete a SIGNIN then I don't think you want to cascade and delete the USER as well.

Answer 2

(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.