Database Design question

[Cthulhudrew]

Maybe some of you guys can help me out here, 'cause I'm a bit stuck. I'm using Access to develop a database that will be capable of centralizing information about various customers. They all share common features (notably, they all use the same fields in terms of contact information), but have different information associated with them as well (some are leases, some are license agreements, etc.).

My problem is I'm having some trouble wrapping my head around what are, I guess, basic conceptual issues with the db itself. Let's say I create the Contact Table, and then one for Lease and License Agreements. If I have a field in the Contact Table that delineates Type, then I could link the appropriate tables to that field so that the db will only create information for the appropriate customers, right? IE, when I create the data entry form, I won't get records popping up for customers who don't need the same information?

I hope I'm not getting too confusing in my explanation.

Anyway, if someone has a suggestion for a good base model for a db like this, I'd be most appreciative!

 

[Mustrum_Ridcully]

I am not an expert on databases, nor am I particularly good at them, but here is what I come up with, anyway. (I don't have any knowledge about Access!)

You will probably need one table that contains the information that apply to all customers.
This table should also offer a primary key identifying the customer (this might be the customer name or a ID you have given him).

For all information that only apply to some customers, you should generate extra tables. Each customer that uses the information of this table would then be also be referred to with the same key as in the "main table".


Customers("Firstname, Lastname", AddressLine1, AddressLine2, City, State, ZipCode, Country, Telephone, Fax, Email)
LicenseAgreements (""Firstname, Lastname, Version", AgreementText)
("" mark the primary key of the table that can be used to uniquely identify each entry of the table)

You can now use the Primary Key of the Customers table as a foreign key in he LicenseAgreements Table to get all available LicenseAgreementTexts for that customer, and might now want to filter by the most recent version.

 

[Andre]

I agree with MR, though I strongly recommend using an ID field as your primary key, instead of the customer's name. Why? Two people/companies can have the same name. You also don't have to remember if you keyed in the company as "The RPG Source" vs. "RPG Source".

That said, I would also strongly recommend some kind of customer lookup form. When using an arbitrary ID for customers, it's quite easy to enter the same customer more than once. Before creating a new customer, always pop up your customer search form and look for data that matches - name, address, phone, etc. - before creating the new record.

As for ensuring that you only display appropriate records, the simplest method is to base the form on a query, which is set up to only pull the records you want, for instance only customers with an active lease. A more flexible way is to create various filters on the form, so you can sort for "Active", "Inactive", "Past Due", or whatever other criteria you might be using. You can tie the filters to command buttons, if you don't have too many. Or you can create another form that has all the fields you want, then uses code to filter your main form when you click a command button.

I admit I'm fairly sloppy when designing db's. I start with what information I want out of the db, then work backwards to determine what fields I need, then group the fields into tables. Once I've created the table relationships (one of the most important steps), I then throw together some quick and dirty tables, forms, and data to test it. Once I've found the most obvious problems with my design, THEN I sit down and work up the real db. Not the best way, but for smaller db's, I find it works for me.

 

[Cthulhudrew]

Thanks for all the help- I thought through things a bit more during lunch and my solution seems to mesh with your suggestions. I just have a tendency to get my wires crossed when it comes to relationships (which is, sadly, probably a metaphor for my life ). I think having separate tables for each type of document (lease table, license agreement table) and then linking them to a Contact table will work out after all.

Also, good idea about the ID key instead of names- some of the LAs I'm dealing with are DBAs and individuals operating with a larger company (thus using the same Name) so I'll need something like that to make it work properly.

 

[Cthulhudrew]

I admit I'm fairly sloppy when designing db's. I start with what information I want out of the db, then work backwards to determine what fields I need, then group the fields into tables.

This is kind of the way I go about it, too.

 

[Nyarlathotep]

I'm no DB expert, but I would recommend the book "Database Design for Mere Mortals". It's software independant and does a great job of explaining how to design tables etc.

 

[maransreth]

The following are a few different website pages that help decide the planning of a database and what is required:

http://www.databasedev.co.uk/general.html
http://office.microsoft.com/en-us/access/HA012242471033.aspx
http://support.microsoft.com/kb/209534
http://www.allenbrowne.com/casu-06.html (while the example is with students and grades, it helps explain database design)
http://www.geekgirls.com/databases_from_scratch_1.htm (this is quite good, esp when you continue the different parts)

I used to teach Access, as well as other MS Programs, and Access and databases in general are difficult to understand if you dont understand the underlying concept. I would recommend looking at the templates that are found in Access and see if one of the Customer or Contact templates that will do a lot of the work for you.

Before even touching Access you need to sit down and think of what you want the database to do, what information you need to store, what you want to get out of the database, etc.
Then you write out all the tables. The easiest way of doing this is grouping like information together. So start with the main table - Contacts: what fields would it contain? Then get the other main groups of information, be it Events, Billing Info, Industries, etc and decide what fields they need.
You then go through the normalising and decide what other tables need to be made, so that you dont double up information, find that you are repeating information in multiple tables, etc. The support.micorost link above gives examples on these. Basically information should only be typed into one table.
To access that row's information in another table you need to have a relationship. To have a relationship however you first need a primary key, ie. a field that uniquely identifies that record. Eg a customer code/ID. That key field is then also found in the related field.

So using your example below some customers will have a licensing agreement. So first you need to have a table of what the different agreements are. Each agreement must also have a field that uniquely identifies it. Now comes the relationship part of things.
To simplify it there are 3 main relationship types - 1 to 1, 1 to many, and many to many. To decide what relationship type you have between two tables you need to ask the following question: "How many time will one of these items be found in the corresponding table?" After that you ask the same question, BUT to the other table.
So continuing with your example - How many agreements could a customer have? 1 or many? How many customers will there be for each agreement? 1 or many? (more than likely multiple customers for each agreement).
Now if you said that each customer will have at the most 1 agreement, you have a 1 to many relationship between customers and agreements. To create the relationship the table that has the many side (in this case the agreements) has its primary key (Agreement ID for example) found as a field in the Customer table (but not as the primary key field, as each customer is a unique record in the customer table).

So the customer table will have the following fields for example -
Customer ID, Customer Name, Address, City, State, Zip, Agreement ID (which is related to the Agreement table), etc.
By having only the Agreement code in the Customer table, you dont have to type out all the words for the agreement, only the code that it refers to.


Now all of this is the underlying table structure and relationship. To make your life and other people who will use the database life's easier you make forms that do all the work for you. On a form you will have a possible drop down box (called a combobox in Access) that lists either the Agreement names, Agreement codes or both and when a person choses the Agreement for that customer, it will put in the Code for that record of the Customer table.

Are you still with me? To be honest I would do some research into who knows Access within your company or within your local area and pick their brains to help you. BUT make sure it is not someone who has dabbled in Access, but actually knows what they are talking about. It might cost you some money to get their help, but it will save you a lot of grief in the long run and your Access database will work.
Access databases are finicky and there are a lot of settings and properties and code that can be done to do different things in the database to make your life easier, but if you dont know about them, you do things the long way or spend forever wondering why it isnt working.