Rare87GT said:
Tell me if my relationships are correct and what I need to do.
Firstly, using
ID as the name for a primary key is not a good practice. Especially when you are relating it to a field in a table with a completely different name.
It's best to use an Autonumber as the primary key but it seems, at times, as if you have used text (i.e. Name which, incidentally, is also a reserved word in Access and not advised) which, when indexing via queries, can slow down the database.
The common practice is to give your primary key the same name as the object the table represents.
i.e.
in a table called tblDepartments, the primary key would be
DepartmentID.
And, when referring to the department in, for example, a table of employees called tblEmployees then it's best just to call your field
DepartmentID
i.e.
tblEmployees - Tablename
EmployeeID - Autonumber
Forename - Text
Surname - Text
DepartmentID - Number
And then, in the relationships window I would just connect
DepartmentID to
DepartmentID.
At the moment I can't really determine much about your relationships when ID at one moment appears to be text but in another instance it's indeterminable from just looking. I can see that you have one-to-one relationships which certainly doesn't appear correct.
Also, with regards to normalisation, the
Cars table can be broken down into further tables for Models, Engines, and Colours.
I don't know what to make of the Inventory table as the field names are plurals which makes me think that you have a repeating group of either:
- checkboxes - where you tick off each of these components;
- numeric fields - where you enter the quantity in stock;
Either of these methods is wrong and is a definite indication that you require a further table for products.
In the correspondence table you have fields called Name and Date. I've already commented on Name being a reserved word but so is Date. In fact,
any word that you use either as a function name, an object type, a data type, or other keyword that Access, by default (or by adding further references), understands, is a reserved word.
Two other things to commment upon:
- When naming tables it is best to use the common prefix tbl. i.e. tblDepartments. The reason for this is that there are a) you can easily recognise it as a table; and b) it stops you giving queries the same name (use the prefix qry for them
) so that Jet doesn't know what is being referred to - the table called Cars or the query called Cars.
[*]When naming anything in Access or VB(A) then it is best not to use spaces or special characters within their titles. The common practice is to gather the name up as one word and to propercase the title. Thus Date Shipped becomes DateShipped. Although Access allows the practice of spaces and special characters this alleviates later problems with coding SQL as referring to items with said negatives means remembering to surround them with square brackets.
