Ms-access – Setting up relationships properly with the Access UI

database-designms accessms-access-2013

I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI.

I have to create a relationship between two fields in one table with one field in the other – have I done this correctly (please see the screen capture below)? If not, what would I do to correct my errors?

Thanks in advance.

Best Answer

I'm with Michael, you should reference the IP ID, not the IP. If you don't link to the primary key, whenever you reference [IP used when creating] or [IP last signin], you are going to get the first record found. e.g. you have multiple records with the same IP in you ips table, you are going to always get the result of the first one. This is why primary keys are so important, they ensure you get the same record every time. If you can assume that a particular IP will always have the same Hostname, Location, Region, etc., then you can sue IP as your reference.

On a side note, if you have to run any SQL or VBA against your database, it's a lot easier if you avoid spaces in your names, use Camel case or something else. e.g. "IP used when creating" should be IPusedWhenCreating. This makes any type of programming a lot easier. I'm very oldschool and I like to keep variables to under 8 characters whenever possible. FirstIP and SecondIP, it just means a LOT less typing.

Related Solutions

Databses design, parallel many-to-many relationships. Two tables, or extra fields

If both fields are relevant in one record (i.e. "used by" and "paid for" can both have a person_id from the persons table), a single table is a better solution. If at least one of the two fields is always NULL, splitting the table would actually make the SQL less complex. (Also, if an expense is either "paid for" OR "used by" but not both, you could instead use an expense_type field instead of 2 separate fields).

As a sidenote, I would recommend against using space in field names.

SQL Server – IS-A Relationship with Single Sub Entity

I would not create another table for verbs because join operations on big tables (especially multiple ones in the same query) may be expensive. I would create one table, with the shared AND the non-shared fields, AND add another column "isverb", set it to 1 for verbs and 0 for non-verbs. You could also later on add "isadjective" etc.

In this case it just makes sense to do:

SELECT * FROM words where isverb = 1

rather than:

SELECT * 
FROM words w 
INNER JOIN verbs v ON w.type = v.id 
WHERE w.type = 2

The application is a dictionary. So it's write once, read a zillion times and so I don't personally see a need for a complicated database design. You essentially just need very fast reads which you can achieve without joins to multiple tables

Best Answer

Related Solutions

Databses design, parallel many-to-many relationships. Two tables, or extra fields

SQL Server – IS-A Relationship with Single Sub Entity

Related Question