Setting up relationships (Access 2003 SP3)

[steveh]

Good morning

I know little to nothing about Acess and Databases. I have been trying to do something in Excel which really should sit inside the realm of a DB, mainly cascading comboboxes. Hans has gindl given me some examples of these in the Excel forum and I am now about to plan my DB.

As I have already said I do not know much about this so I have drawn out my idea of what the relationships should be bearing mind that sooner or later I will want to filter the results of Customer to Study and then down to Site. Can somebody advise if I am going in the right direction before I start building.

[JohnH]

A few things:

• Just what things are called. The whole thing is a database. Each of the things you call a database above, is called a table.
• Each table has a primary key. Good. Relationships between them come via Foreign Keys. You have some but the major ones are missing. What is the connection between Customers and Studies? Does one Customer participate one Study and each Study have multiple Customers? If that were true the Customers table needs a field to indicate which Study they are in - St-Name or whatever is the Key field of the Study Table.
• Same question applies to the relationship between Studies and Sites. What is going on between them?
• There are some advantages in using number fields (autonumbers usually) as primary keys, so I think I would add a StudyID field to the Study table and use it as the key.
• What is going on with the multiple Shipping Types and Packaging fields? Do they come in pairs? In general when you find yourself repeating fields they belong in a separate related table.

[Wendell]

In addition to John's excellent points, it appears to me that you may need a linking table that tells you which customers are participating in which studies. Linking tables are tables that contain a pair of foreign keys that allow a many-to-many relationship between two other tables. The same may be the case with the relationship between studies and sites.

Also, you might want to adopt a formal naming convention for field names (sometimes referred to as columns) - one of the more commonly used is the Leszynski Naming Convention. To fully implement it can be rather intimidating, but I find that it is useful to identify the type of object you are dealing with (tbl=table, qry=query, frm=form, rpt=report, etc.) and for fields, prefixing them with the type of data they represent, i.e. int=integer, lng=LongInteger, dt=date, cur=currency, str-string, etc.

[steveh]

Hi Wendell

Thank you for the excellent advice.

A Customer (Account) can have dozens of studies but they are always unique to that Customer, Sites can be shared by several studies but I thought it might be better to duplicate the details because when a Site is set up for a Study there may be different departments, names, etc. as well as supplies so all the core may remain the same overall they are unique to that Study.

I read about the way I have named the fields on an MS Access site that suggested that if use several tables all with address in you should use a way to distinguish them such as Cu for Customer, looking at the accepted naming version you have posted in the link now makes me realise this is wrong so i will study this in more detail.

[Wendell]

Well, where you have different kinds of addresses, it is probably a good idea to distinguish a site address from a study address from a customer address. If you make them all the same, then you do end up with an issue on queries where you have to qualify the table name involved when you create expressions, or Access complains. I have worked with designs where all of the Addresses were stored in a single table, and they were qualified as to type. In fact we have a large application that stores phone numbers, emails, and websites in a single table with a type indicator that is used to determine what kind of electronic address it is.

[Charlotte]

I read about the way I have named the fields on an MS Access site that suggested that if use several tables all with address in you should use a way to distinguish them such as Cu for Customer, looking at the accepted naming version you have posted in the link now makes me realise this is wrong so i will study this in more detail.

I know that concept has been around a long time and I even know people who swear by it. I personally always found the Leszynski convention to be more practical and easier to read. Frankly, I suspect the reasoning behind it (prefixing fields with table name indicators) boils down to laziness. You can pull all the fields from various tables into a query without having to alias them. Since all that gives you is a poorly designed query, I've never accepted the concept. Doing that way also makes it impossible for Access to suggest joins based on field names, which it happily does if you have avoiding naming them with table prefixes.

[steveh]

Hi Charlotte

Thank you for the input, I am still wading through and have taken Wendell's advice on the naming and as I can see from your comments it should make it easier going forward.