so i have a design like this till now:
CARS : license_plate_num (PK), car_type, req_driving_license, price_per_day
DRIVERS : driver_id (PK), driving_license_class, price_per_day, id_number (FK)
CON.DETAILS : id_number (PK), cell_phone_private, cell_phone_work, landline_phone_home, landline_phone_work, email, address, name, surname
CUSTOMERS : serial_num (PK), id_number (FK), driving_license_class
ORDERS : invoice_number (PK), serial_num (FK), driver_id (FK), car_rented, from, to, car_returned_ok?
PAYMENTS : invoice_payment (PK), payment_type, amount_paid, date
LICENSES : license_type (PK), description
PAYMENT_TYPES : payment_type (PK), description
the way i organise my contacts (and you can apply this concept to any data series) is more like this:
tblCustomers : CustomerID (PK), Salutation, NameFirst, NameLast, DOB, Notes
tblCustomers_Contact : ContactID (PK), CustomerID (FK), ContactTypeID (FK), Cell, Landline, Fax, Email, Street, Suburb, PostCode, State, Country
tblCustomers_Contact_Type : ContactTypeID (PK) (e.g., "private", "work")
this is how the above works: many people are put into the customers table:
person one has no job but they can be reached on their home landline, their personal mobile, or by email. so in the customers_contact table i select the person and the type of contact i'm adding - in this case it will be "private" (via the ContactTypeID (FK)).
person two has a job, and they prefer to be called there. so i put in the person details, then in the customers_contact table i put in a record and call it "work" (via the ContactTypeID (FK)).
person three has a job, but they work nights, however it turns out i may need to contact this person at any time of day, so i need both his home and work details, so in this case i put in two records for this one person in the customers_contact table, one for "private" and another "work", and then use the available fields to fill out the multiple cell, landline and email details.
now, if the home and work fields were both in one table, you are wasting lots of bytes storing empty fields for people who don't need them. you only need one field each for e.g., cell, landline and email. the ContactTypeID is what will distinguish between them.
the other thing i'll say is about how i've named my tables.
against the warnings of namliam (any many others on this topic! sorry, i just like the tidyness it offers), i actually do use the underscore (_) character in my tables to denote that it's a 'sub' table of another (i also do this with forms and reports, as it is common to need subForms or subReports).
e.g., the table tblCustomers_Contact_Type is a table from which tblCustomers_Contact will get more information (in other words, tblCustomers_Contact_Type provides the list of items ("private","work") via a drop-down (or combobox) to the table tblCustomers_Contact). if in the future you decide you need MORE options for contact type, it is easy to simply add new contact type and then have customers assigned this extra contact type
a lot easier than having to put in a new set of contact fields into one table (i.e., cell_phone_private, cell_phone_work, cell_phone_work_2, cell_phone_oveseas is better constructed by having one table "private", "work", "overseas" that you link to the contacts table and then fill out fields that are already there for the Cell.)...
one more reason i name my assets like this is because then they are automatically 'grouped' in the assets box (in A2007 it's the "navigation pane", i believe) b/c access orders the tables etc alphabetically, so any tables that are FK to the customer table, for example, are listed in access together. i won't have to remember that "tblType" is a subtable of "tblContact" b/c if they're named "tblContact" and "tblContact_type" it will be without a doubt and clear as day and obvious that these two tables somehow belong to each other.
a good way to know if you are putting in redundant fields is this: if you are naming your fields: (name, date,
something_1,
something_2), then that is a sure sign that there probably should be a separate table called
tblSomething, with the different
Something options in that table. it allows for easy expansion when you require it, but also streamlines the db such that precious data storage is left available for those important records that need to be saved.
to the gurus: please let me know if this is a faux pas! seems to work for me pretty well, though.