How best to split a monster table

[khayward]

Hi,
I'm a new member, I have limited experience with design, I have successfully built a few tools that I use to keep track of crimping equipment at my shop (Crimp terminal specifications, crimp tools and which go together) as well as another simple one which allows me to see information about all the times a product has been built in a specific year. I'm trying to build a simple work scheduling database but it is really testing what little I know and I need help. I have a learning disorder and I'm struggling with the more abstract parts of what I want to build.


The goal is to keep track of purchase orders but everything I have read says my initial thoughts were wrong. I have two large tables which were pulled from an existing spreadsheet tool and rearranged slightly - a master list of assemblies and a table for purchase orders. What I need help with is where do I break things up into separate, narrower tables. I have read plenty but I'm having trouble understanding. I get the impression that I need to use queries for this also but there's a mental block there. I've put a screenshot of the relationships window below if that helps. Please feel free to provide any criticism, I'm not afraid of being wrong or corrected!

[Bob Fitz]

Just a few observations and recommendations, for what the're worth.

Remove spaces from object names like fields. So, On Time becomes OnTime
Do not use "Special Characters" in object names like fields. So, Job # becomes JobNum. I would avoid them in the actual data as well.
Read up on database normalization. Field names that have numbers in them suggest tables are not properly normalized.

[June7]

Advise not to use spaces or punctuation/special characters (underscore is only exception) in naming convention. Better would be: tblSchedule or Schedule, MasterAssemblyList or Master_Assembly_List, MtlLotNumReq or Mtl_Lot_Num_Req. Not seeing any reserved words (such as Date or Name) used as names, that's good.


You have 6 sets of similar named fields, such as: Unit Price 1, Unit Price 2 ...

You should have a dependent (child) table that would be related to the parent MasterAssemblyList.

MasterID	PriceBreakQty	StdMtl_ea	StdTime	UnitPrice
1
1
1
2
2

You are not linking Schedule and Assembly on the defined primary key. Is this intended to be a 1-to-1 relationship? Will there always be related records in both tables? Might as well be 1 table. Which is probably doable after breaking out the repetitive fields into dependent table as described. Or should the Assembly ID primary key value be saved into schedule as foreign key? Can each assembly record be associated with more than one schedule? More than one Company? Or is Assembly a junction table that links Schedule with Company in which case the Schedule ID should be saved into Assembly?

Also, fields are duplicated between Schedule and Assembly, why?

[khayward]

Thanks to you both for responding. I'll try to explain things a bit. I have been reading about normalization, though I'm quickly overwhelmed when trying to think it through. I struggle with taking the general principle or abstract idea of something and applying it to a specific situation - I tend to bounce between a torpid stupor and gestalt learning and I haven't been able to find a learning approach that works consistently.
I did read a link about something similar to this earlier today. So, the reason for the six sets of fields with numbers at the end is because each assembly could feasibly have up to six different unit prices depending on quantity ordered. I can see after reading these responses and the article that I would need a separate table to deal with this. I'm guessing from the example provided this would be one table for all assemblies with MasterID being tied back into the MasterAssemblyList table - am I understanding correctly?

Anyhoo, the background to this is that I had a spreadsheet with three tabs: Archive, Schedule and Master Assembly list. When a PO is received the information from the master tab (Information about each assembly) is copied over to the schedule tab and Purchase Order specific information is added to relevant cells. Once the Purchase Order is completed and closed out the whole row is cut from the Schedule tab and pasted to the Archive tab. Hence the MasterAssemblyList table and the Schedule table, they're essentially the same as the spreadsheet. I want to set up order entry with a form, I know a lot of the information in the Schedule table is redundant but I wanted to be able to look up purchase orders by job number or by assembly number with the same form used for order entry and be able to see other information in the form, so I wasn't sure how to break everything up for that and just kind of dumped all the information there. I have a sense that the Schedule table needs to be a huge deal narrower - if I got rid of a lot of the stuff that's duplicated from the MasterAssemblyList table could I still have the form display related information about an assembly when a Purchase Order is being looked up?

I have some time while dinner is cooking so I'm going to mess with this a bit more, Thank you both again for your responses!

EDIT: I did have a bit of time and did some work, I hope this is beginning to look sensible:

[June7]

Why are StdMtlEA and StdTimeEA and UnitPrice fields duplicated in Schedule and PriceBreakstbl? Some field names still have spaces.

Most experienced developers would put 'tbl' at beginning of name. Similarly for qry, frm, rpt.

[ArviLaanemets]

Hi

Hence the MasterAssemblyList table and the Schedule table, they're essentially the same as the spreadsheet.

Then there is no need for 2 tables at all! Add a Boolean field IsActive to table - it will be True for active schedule, and False for archived one.

On form with shedules, set filter for form 'IsActive=True' to active shedules only to be displayed. The form filter set to 'IsActive=False' displays only archived shedules. For setting the Form filter a combo box can be used.

[Micron]

One field has a question mark ?, another has # , another "/" etc. - should not, as mentioned.
Upon reading some of the feedback without information links - some suggested reading (I know, it can be frustrating but we can help walk you through as much as possible).

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

A key part of understanding normalization is grasping the entity relationship theory. I empathize with your struggle, so will try to provide both a link I like for normalization as well as a more simplified explanation (or at least an attempt).
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/My own thoughts that I hope will help you with grasping normalization a bit better:

You are an entity, and as such you have attributes (hair color, eye color, height, gender, first name, last name...) notice that I did not combine colors and names as any of your attributes. The information about you that is pertinent to the business case could go into one table, and something that uniquely identifies the table row you are on is needed. Your last name would not be a good candidate since there could be more than one person with that name. Nor can the first and last name combination be relied upon to be unique. Nor is your phone number a viable candidate to uniquely identify you. What then? Perhaps as an employee, each one ever hired will get a unique number which will never be reused. That would work, and so would an autonumber. Personally, I'd use the former in that case, but that's another thread since there will be dissent no doubt. So the ENTITY becomes the TABLE (tblCustomer; tblDbUser; tblPO; tblWorkOrder; etc) and each ATTRIBUTE is a table FIELD.

What follows is my opinion and there may be those who disagree. Focus less on the example given and more on the theory.

Let's consider customer contacts as you have them. Is a customer contact an attribute of a customer? Stuff like this can be tricky, and the answer can be yes or no (it may depend on your business case). If there can be more than one and you start laying them out in fields (as you have), you are not really following normalization rules. Rather, a spreadsheet mentality is creeping in and you should forget what you know about spreadsheets when it comes to db design. To put it simply, spreadsheet data is laid out in columns, db data goes in rows.

IMHO, the answer in your case would be "no". Unless you know you are never going to accommodate a customer who insists on identifying 2 contacts when you only have 1 contact field, then go ahead and put in one contact field. But you have 2. You should never have to add or remove fields to/from a table for an attribute of the same type, so what if someone demands 3? Are you going to annoy them by saying they can only have 2 because that is how you set it up? Generally speaking, I would not have 2 fields in the same table for one attribute type. Rather, I would have tblContacts with the CompanyID (or whatever tblCustomers field uniquely identifies a company) repeated for each record that a contact has been identified for a company. Then I can have as many contacts for a company as that company likes, and with queries I link the contact info to the customer by CustID. My tblContacts might look like this:

CustID	FName	Lname	ContactPhone	ContactCell
22	BOB	SMITH
22	BOB	SMITH
22	BOB	SMITH
28	MARY	JONES

So are all of those fields in "Schedule table" (not a good name for reasons mentioned) attributes of the Schedule? Only you know for sure at this point. I do wonder about quantity backorder field - if this is calculated, generally it should not be stored, especially if it is subject to change.

"Type" is a reserved word and should not be used. Make sure you bookmark my second link above.
Maybe that's enough for you for now...

[khayward]

Why are StdMtlEA and StdTimeEA and UnitPrice fields duplicated in Schedule and PriceBreakstbl? Some field names still have spaces.

Most experienced developers would put 'tbl' at beginning of name. Similarly for qry, frm, rpt.

I have done some updating, but I wanted to answer the question, in case I'm needing to do something important here. The goal is to have data entered about purchase orders, but the actual unit price could be one of up to six possible unit prices for each assembly depending on quantity. Perhaps I am still stuck in Excel land, I'm trying to have a steep learning curve! I was assuming that once the unit pricing had been chosen from the price break table it would need to be recorded for that specific purchase order (Orders are not always the same quantity, so each order for a particular part may have a different price, and the price break quantities aren't always the same from assembly to assembly).
I hope that explains the situation, even if I am modeling things wrong. Again, thank you so much for responding!

[khayward]

Let's consider customer contacts as you have them. Is a customer contact an attribute of a customer? Stuff like this can be tricky, and the answer can be yes or no (it may depend on your business case). If there can be more than one and you start laying them out in fields (as you have), you are not really following normalization rules. Rather, a spreadsheet mentality is creeping in and you should forget what you know about spreadsheets when it comes to db design. To put it simply, spreadsheet data is laid out in columns, db data goes in rows.

IMHO, the answer in your case would be "no". Unless you know you are never going to accommodate a customer who insists on identifying 2 contacts when you only have 1 contact field, then go ahead and put in one contact field. But you have 2. You should never have to add or remove fields to/from a table for an attribute of the same type, so what if someone demands 3? Are you going to annoy them by saying they can only have 2 because that is how you set it up? Generally speaking, I would not have 2 fields in the same table for one attribute type. Rather, I would have tblContacts with the CompanyID (or whatever tblCustomers field uniquely identifies a company) repeated for each record that a contact has been identified for a company. Then I can have as many contacts for a company as that company likes, and with queries I link the contact info to the customer by CustID. My tblContacts might look like this:

CustID	FName	Lname	ContactPhone	ContactCell
22	BOB	SMITH
22	BOB	SMITH
22	BOB	SMITH
28	MARY	JONES

So are all of those fields in "Schedule table" (not a good name for reasons mentioned) attributes of the Schedule? Only you know for sure at this point. I do wonder about quantity backorder field - if this is calculated, generally it should not be stored, especially if it is subject to change.

I appreciate the thorough response, it's a lot of food for thought, I did look at the contacts information and I can see what you're pointing out. I broke out the contact information from the customers table. I also need to create something for shipping information (What carrier, how long it takes to get there). I am going to try and make some notes on my screenshot and see if I can describe some of the characteristics of the fields that are there at the moment. I'll also read the links you recommended and see how much I can comprehend. I really appreciate the information and help! I've been wanting to learn more since first taking database classes in college. I took an Access class and a couple of SQL classes but could never wrap my head around it, plus, they were online and the instructor wasn't very available. Again, I really appreciate the information and feedback!

[June7]

up to six possible unit prices for each assembly depending on quantity

Okay, understood. Does user have to make the price selection or do you want to automate associating price with quantity? This can be done with your original structure but what is the likelihood of someday needing more than 6 price options? Adding another would mean redesigning table, query, form, report, code. However, I expect this has low probability of occurrence. If the options were arranged vertically then a query joining tables could probably work to associate individual price, and adding a new option would be easy. In either case complication arises if the quantity breaks and/or prices are ever modified and existing records should retain the old values. This can be managed in either structure by saving price to record, which can be automated with code. Another approach is to save price break record ID. Flag obsolete records as 'archived' and exclude from consideration for new records by filter criteria. Create new records for the new pricing.

IMHO, it is a balancing act between normalization and ease of data entry/output. More reading material http://www.agiledata.org/essays/dataNormalization.html

[Micron]

You can imagine the problems that would arise if you stored only prices in a table, referenced the price row PK (primary key) on an order and calculated the line total based on quantity - then next year changed the price. When you opened that order after that, all the amounts would change. This is one of the key exceptions when you read that you should not store calculations. As noted by June7, if price history is important, you can do it based on archiving price table rows but I'm not sure it would be worth the extra design considerations plus the ever expanding price table row count - unless you want to report out on price fluctuations. If all you'd ever need is to know what the price was at some point in time, you could query against your PO's (or whatever) for that product ID. You would store the price and quantity on the order line (and the discount rate if it is a line factor), and allow the line total control on the form to calculate the line total. If the rate is applied only to the whole order, or doesn't need to be a line item, then it could go on the PO record instead of the PO line. The rate can be calculated as J7 says, whether or not you calculate it on the form/report in a text or combo box or whatever. Just don't calculate it in a table.

[khayward]

Thank you both for the reference materials, I have been reading through and I think I've found a sticking point in my thinking. I kept seeing mention of 'Spreadsheet mentality' and it finally clicked this morning that the biggest part of that for me is the notion that if I want to see certain information it all needs to be in the same table. I, and my users, are not going to be interacting with the tables, we'll be using forms, reports and queries, which can pull information from anywhere in the table. I hope I got that right. This was a big worry for me - if the scheduling table has only the assembly number in it then how would anyone using a form for order entry see any of that other information...
Another useful thing I read was from the Rogersaccessblog link - he mentioned focusing on the relationships and entities and not on the data. That's what I'm trying to do here - focus on the table structure and not worry too much about how it will all behave when people use it. It's hard to get away form thinking about a finished product!

Anyways, thank you again for all the help, I hope to post a progress screenshot of the tables later today and we'll see how I'm doing!

[Micron]

Great news! Now that you've crossed into another realm, I'll add my own personal rule of thumb (which likely not everyone here agrees with). Queries before forms.
If you cannot update test records (including delete) in a query yet you expect to be able to in a form that will represent your data, then what good will the form be after you build it? Either the table relationships are incorrect for what you're trying to do, or you intend to base the form on the type of query that is not updatable by design. Better to know before you spend time on a form.

Consider subforms on parent forms. You cannot update the many side of a one to many relationship if you have defined the relationship and don't have the necessary data on the "one" side that links them. It's easy enough to add the minimum required test data on the one side (in the table, or better yet, via a query you intend to use for the main form) THEN try to update the many side. If it works, it should work on the main/subform that you build.

scheduling table has only the assembly number in it then how would anyone using a form for order entry see any of that other information...

Think of your records in this over-simplified way. You have 3 buckets (tables); one with golf balls, one with ping pong balls and one with gum balls. You need to assemble a group (one record) based on attributes (table fields, remember?) by drawing from each bucket. You are Access, and I hand you a piece of paper (criteria) that asks you to retrieve one red item from each bucket. You now have 3 items (a complete record) all related by the color red (the attribute). And so it goes for things like PO line items - get me all the line items from tblPOLine for PO3456 (the 2 tables being joined by PO number). We can also get the parts from tblParts by joining between the POLine part number and tblParts part number. Again, it was simplified - the import thing being to add is that we can join table A to B on one attribute, and B to C on a different attribute as long as the table in the middle contains both. This is expandable to many tables for sure, but I don't want to complicate things at the moment.

[khayward]

I'm glad to know that I appear to be on roughly the right path, I was only able to spend a few minutes here and there tweaking the tables today, but here's where I'm at:



I do still need to work on field names and so forth, plus I think there are still some things that can be eliminated or broken out of what's here, but I feel there's some progress, so I'll take that!

As I am reading more I am seeing that I will have to learn about queries. It's a bit daunting so I will keep my head down and focus on the relationships and tables first. Forms I think I will need less help with but then again.... I've used forms in Access - with some success - but never for this sort of stuff.

Thanks again for all the help!

[ssanfu]

Maybe another suggestion??

The first field in a table should be the primary key field.
Beneath the PK field should be the foreign key fields.
Then the other fields......

For the PK field, I use a suffix of "_PK".
For the foreign key field I use a suffix of "_FK".

This accomplishes two things:
it is easy to tell at a glance what all of the FK fields are in a table.
And in a query, you can tell which is which - it gets confusing looking at a query in design view with multiple tables, which fields are PK fields and which are FK fields. If you have a query with multiple tables and you see a field "Customer", is it the PK field or the FK field??