Breaking down access tables, how far is too far?

**mchadw** · 08-14-2011, 01:18 PM

I'm working on my first very large access project. My customer has a medium sized business with multiple divisions, each with their own processes, management tools (web/local), and unique problems. They all must go through Coorporate, where they have to be unified for payroll, accounting etc... It's a mess every week. Although each division is relatively unique it is fairly normal to have 1 person simultaneously categorized as a customer in more than one, as well as an employee in one, and a contractor in another (not to mention where he/she may have been classified in the past).

Now for the question: Is it going too far in the name of breaking information down to its smallest parts, to have say, 1 table for all people? This table would include only the few constant particulars as fields (Ssn/dob/gender etc...) and is related to multiple other tables that's would detail the particulars of any other category said person fell into, using query based forms? Am I making this too difficult? Or is this a good practice vs. Having separate customer/employee/contractor tables?

**MarvinP** · 08-14-2011, 01:59 PM

Hi mchadw,

I recieved an Access DB last week with 70,000 names in a Genealogical database. Names were scattered between Main, Spouse and Parent tables. It is a nightmare!! This DB also has no relational links defined and stung together using VBA code for the links between tables. I with the builder would have put all the Names in a single table so it was easier to understand and deal with.

I just searched on Best Practices for Database Design and there are lots of suggestions. I'd be reading
http://office.microsoft.com/en-us/ac...001224247.aspx
for more help.

I'd keep all people names in a single table. I'd have another table for Divisions. Then I'd create a table for the links between the Names and Divisons. KISS will help in the long run.

**mchadw** · 08-14-2011, 05:26 PM

Thank you for the advice Marvin! That is exactly the kind of issue I'm trying to head off at the pass. Just one department, of one division of this company processes over 75k orders a year, from over 50k unique customers. They still use paper, and each department has it's own version of each record, organized into it's own independent spreadsheet and location (There are 17 different spreadsheets, in 17 different locations, that must be updated with duplicate information by multiple coorporate departments before a new hire can even use a stapler. The major portion of their resources are still consumed just processing their information manually. I'm very excited about this project.

Please understand though, I am a novice at best. I can navigate the software with ease, and understand the rules individually, but still don't always grasp the entire picture in one thought. So, I hope you'll allow me to clarify.

I've read the contents, in the link you provided, and still find only inderect reference to this practice. In the final "table outline" for example:

The name of a person appears in
tblCustomers.[fldName]
tblSuppliers.[fldContactName]
tblOrders.[fldSalesperson]

If I understand, you are confirming that a better practice would be the following OVERsimplification:

tblPeople.[fldName] 'KIS and not stating the obvious fldFirstName, fldMiddleName etc...

tblCustomers.[fldCustomerName] "FROM...." tblPeople.[fldName]
tblSuppliers.[fldContactName] "FROM...." tblPeople.[fldName]
tblOrders.[fldSalesperson] "FROM...." tblPeople.[fldName]

Does this make sense? Am I "Getting it"? It makes sense from an efficiency standpoint; but does a proper relational DB really consist of so many tiny tables? All the "Real Work" is done in the relationships, and the front end?

**MarvinP** · 08-14-2011, 07:20 PM

Building your tables is both art and science. You need to insure you can get to what is needed at the end. In my head you need a tblPeople and a tblProducts and a tblDivisions as these are independent and obviously different.

When you suggest a tblCustomers and another tblSalesperson that doesn't feel right. What is f salesperson wants to buy something in the function of a customer?

I believe all the work is done in the links table. Person buys from person would be a linking table. With date stamps in the sales table you can have person buys from person and filter by date.

I hope I'm not smoking bad dope!!! The relationships need to be examined to see if you can create the reports needed. BUT - keeping an obvious structure is very important.

**mchadw** · 08-14-2011, 07:53 PM

I think im with you, except one thing... Can you tell me what you mean by "links table"? Is there some other way to manage relationships like that?

**MarvinP** · 08-14-2011, 08:14 PM

In my mind a links tables is a table with two fields. It is used to tie two other tables together. Say you have a table of Divisions and a table of people. You have 5 divisions and 100 people. This link tables would have two column with data like 1,5; 1,7; 2,8; etc. This would put person 5 and 7 in Division 1 and person 8 in division 2, etc. I call this small table a link table that is the glue between who is working in what division.

I hope this makes sense.

**mchadw** · 08-15-2011, 08:51 PM

Precicely what I was looking for! I've been struggling with the direct relationships I've been using, and the fact that each of them, stopped me from making another one, or stopped me from using them both in the same query without altering the relationship for the query alone (which doesn't seem like good practice in my head either). That did it! It solved another issue I've been having too. Within each division, we provide services to much larger companies, whos structure is broken down into nations/regions etc... I've been trying to create an append table query (to upload reports we are supplied) that would cut our storage requirements by more than half, by makeing the appropriate link to data in an existing table, rather than storing it as an unrelated field. None of my attempts would work across the board, requiring multiple queries to suit only slightly different requirements. Two links tables and, voila! Thanks again MarvinP

**davegugg** · 08-17-2011, 04:47 PM

I'd suggest taking a close look at the last section (regarding Normalization) of the link MarvinP provided. This is an important concept that can make working in a database 1000% easier. Once you have a firm understanding of how a relational database should be set up (and I admit it took me quite a while), the world of data management is your oyster

.