How best to split a monster table

[khayward]

Thanks for the suggestion, it makes a lot of sense. Just so I understand things properly, to follow your suggestion I would want to add the _PK suffix to the Customer field in tblCustomers and add the _FK suffix to any other place the same field appears, such as tblContact or tblMasterAssemblyList? I hope I'm understanding correctly, your comment has made me realize that I need to read up a bit more on foreign keys, primary keys and indexes (Not the correct plural, but it's still early), I thought I understood but perhaps not.

[khayward]

I'm trying to understand how this will work as I'm getting to the point where it looks like I need to consider what fields exist and where.
What I'm hoping to achieve is that once the order is entered and the user has selected the price break for that specific order it won't change when the tblPriceBreaks is updated with new unit pricing, as it inevitably will.
The row in tblOrders (I changed the name from tblSchedule) will, I hope, be a permanent record of that order that won't be updated when the price or other attributes are updated. I'm not sure how to approach this - should there be a separate table for active and closed orders, or a checkbox for open/closed?
I'm guessing the calculation would take place in whatever form is used for order entry and be written to the table? (This is the point where I begin to get overwhelmed trying to map stuff out in my head).
Obviously I need to address this while I'm still determining the structure, seems like it would be a bear to go back and change things later...

[ArviLaanemets]

Hi

There are 2 ways:

1. You have a CURRENT price list. When you create a new order, a current price from price list is written to order row price field, and is never recalculated. You may allow on specific conditions to edit this price (p.e. until the order row is saved), but mostly it will be better to keep it locked. Whenever needed, the price is read from order row table.

2. You have a price list with price start date (i.e. you can have several prices for same part with different price start dates, but never with same date). You also must have a function, which returns the right price for part from price list at any date. To cut off the possibility of meddling with prices in past, you have to lock all records in price list older than some reasonable time interval. For order row, you store quantity and part ID, and for order, purchase/selling date. The price is calculated based on this data whenever needed, using the function I mentioned before.

[Micron]

if one assembly table assembly number truly can have multiple price breaks AND that assembly number can be on the same schedule multiple times, then you appear to have worked out the relationships. I can't be definitive because only you truly understand the business at hand.

Most of my db's at work involved ODBC tables. If memory serves, somehow the PK fields always showed up in query design as bold field names. Wouldn't that be nice on the Access side?

[khayward]

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

Hi,
I've been focusing on getting the tables split up for the last few days and deliberately avoided looking at these details to avoid being overwhelmed. Anyhoo, The quoting process I have built only allows for 6 price breaks. Those can be any quantity value but there's only 6. I came to the number based on the maximum number of requested price breaks over 4 years, so that number is pretty much fixed. I have the separate breakout table for price breaks now so hopefully that's enough flexibility to handle additional price breaks in the future.
Once the price breaks have been selected I'd like them to stay the same - that way when I look at old orders the information is accurate. What would be good is if, once the price has been selected, the form or table calculated the total order value as well as the total time required for the job. In addition I would want it to do the simple division required to provide the percentage yield (Total StdTime divided by Total ActualTime). Is it best to do these in the order entry form?

[khayward]

I feel like this will be a flurry of activity! I'm posting frequently, if that's causing issues I apologize! Below is how my tables look now. I read an article that said my tables should be tall and thin, not wide, so with that in mind I am wondering if it would be sensible to break out the customers table to have billing, shipping address info as two separate tables, as well as a third table with shipping information.
I'm also considering breaking out the orders table but not sure if that's wise (I'm thinking of breaking it out into shipping info (Due date, date shipped etc), productivity info (StdTimeEA, StdMtlEA etc) and items specific to the order, such as PO Number, job number and so forth. I would be happy to receive feedback about further splitting tables!

[ArviLaanemets]

I am wondering if it would be sensible to break out the customers table to have billing, shipping address info as two separate tables

Get a table CustomerAddresses: CustAddressID, CustID, ZipCode, Country, State, Street, CustomerName, IsBilling, IsShipping

When billing and shipping addresses are same, one record with IsBilling = True or 1 and IsShipping = True or 1 is enough.
When billing and shipping addresses are different, you need one record with IsBilling = True or 1 and IsShipping = False or 0, and another one with sBilling = False or 0 and IsShipping = True or 1.

So you can even have several different addresses for same customer - simply add some info to CustomerName to differ between them. Of-course then you have somewhere do define, which address you are using in current document - when you have always only one address for billing/shipping, then you can read it drom CustomerAddresses table by default (but consider it as bad practice).

And you can have an additional field, indicating is the address valid or not. So yo can have old addresses for old documents, but new ones are created only using currently valid addresses.

[ssanfu]

Just so I understand things properly, to follow your suggestion I would want to add the _PK suffix to the Customer field in tblCustomers and add the _FK suffix to any other place the same field appears, such as tblContact or tblMasterAssemblyList?

Yes, that is the idea...

I hope I'm understanding correctly, your comment has made me realize that I need to read up a bit more on foreign keys, primary keys and indexes (Not the correct plural, but it's still early), I thought I understood but perhaps not.

See
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

All of my tables have an autonumber PK field (see the first link). Doesn't hurt anything and it is there if I need it later on.

====================
Looking at relationships image in Post # 21:

Looking at tables
tblCustomers and tblContacts:
One customer can have many contacts and one contact has one customer. OK - a one to many relationship.

tblCustomers and tblMasterAssembly:
One customer can have many Master Assemblies and one MasterAssembly has one customer.

tblMasterAssembly and tblOrders:
One MasterAssembly can have many orders and one order has one MasterAssembly


I am probably wrong, but I would expect one customer to have many orders and one order to have one or more Master Assemblies

[khayward]

====================
Looking at relationships image in Post # 21:

Looking at tables
tblCustomers and tblContacts:
One customer can have many contacts and one contact has one customer. OK - a one to many relationship.

tblCustomers and tblMasterAssembly:
One customer can have many Master Assemblies and one MasterAssembly has one customer.

tblMasterAssembly and tblOrders:
One MasterAssembly can have many orders and one order has one MasterAssembly


I am probably wrong, but I would expect one customer to have many orders and one order to have many Master Assemblies

Hi, thanks for your response. In the structure I want to have each record in tblOrders will have only one assembly associated with it. We often get a purchase order with many lines, but we treat each line as one job, hence the PO number not being the primary key. And yes, each assembly number in tblMasterAssembly goes to only one customer, we're a contract manufacturer rather than a widget manufacturer so each assembly is specific to a customer. Does that make sense?

After I hit send I thought on your post a few minutes and I think that I need to break PONum out into a separate table, because of the nature of our orders it's possible for one purchase order to have a number of assemblies associated with it. I had previously thought that the PO number would just be informational but it seems like better database etiquette, from what I've learned in the last few days, to break it out so I can establish a relationship.

I really appreciate everyone's feedback, you're all really helping me to think in terms of making one-to-many relationships and eliminating duplicate information, thank you all so much!

[khayward]

Hi again everyone! I've done a bit more reading and a small amount of reorganizing associated with my tblOrders and some renaming of things. The main thing I did was to break out the fulfillment information from tblOrders because I do occasionally have to make multiple shipments against an order, if I understand what I did correctly I can now have multiple shipments for each order. I also broke out productivity info from the same table. This was only done in the hope of making the table narrower. All the reading I've done gave me the impression that I had too many fields in one table. I also added a shipping information table - we maintain a list of which carrier each customer uses and the account numbers we use for each customer and my office person said it would be helpful to have that information in there, which made sense.

As always I am absolutely open to feedback and criticism. Once I've stared at this thing a while and not found anything else to add or break out I'll begin structuring the fields. I did this for tblContacts already - setting the field format, adding an input mask and so forth. I will need to go through all the tables for this next, most of my fields are set as text/255 field length - my understanding is that this is not preferable for database size.

[Micron]

All the reading I've done gave me the impression that I had too many fields in one table

This decision should be based on whether or not the fields in question are attributes of the entity and/or if there is a need to break out some of them into a new table because there would otherwise be additional rows with lots of holes (like what you've described for multiple customer addresses. It should not be based on some arbitrary limit on the number of fields in a table. There is after all, a 255 field limit IIRC.

setting the field format, adding an input mask and so forth.

If this means you intend to enter data directly into tables as the normal course, then I would advise against this.

most of my fields are set as text/255 field length - my understanding is that this is not preferable for database size.

If you know that a field will never contain more than a specified number of characters, it will help to keep the db size down a bit if you set the size lower. However, if you don't also endeavor to keep the db size down through other means (such as occasionally compacting/repairing; turning off Name Autocorrect, etc. then it probably won't matter much.

[khayward]

If this means you intend to enter data directly into tables as the normal course, then I would advise against this.

My intention is to interact with forms. I was formatting fields in an effort to try and control and structure data as well as having it appear a certain way. The input masks I applied were for phone numbers and dates, all picked from the wizard. Also, some things, like job numbers, are a fixed length so I saw no need to have a text/255 field for data that will always be a 4 or 5 digit number.
I was pondering the structure this morning and thought of a potential problem - I'd like to be able to review assembly history and have each previous job be a snapshot of thst assembly at the time it was ordered. Assemblies change over time, usually the revision level, time (StdTime), materials (StdMtl) or unit price. With the structure above I feel like if one of these attributes changes it will make having an accurate history difficult. My gut is telling me that when an order is entered those changeable attributes need to be written to tblOrders in order for that to heppen, does that make sense?

Thank you all again for all your advice!

[Micron]

My gut is telling me that when an order is entered those changeable attributes need to be written to tblOrders in order for that to heppen, does that make sense?

My thinking is no, but they do probably belong in some sort of assembly table (likely the master one that you appear to have now). Then each version of the assembly can be kept, with an assembly ID being part of the order record. You don't have to show the assembly record ID on any form, but it will govern which version of the assembly (therefore, which parts it's comprised of). I presume you will set this up so that each component of an assembly will be a row in a table like tblAssyParts. You might find that 9 out of 10 parts are the same for an assembly and only one assembly record differs from the rest for any one assembly. I'm seeing this as a composite PK (or composite index as some here seem to prefer) with the combination of AssyNo and PartNo comprising the composite index. Hopefully you will not put the individual assembly components into their own fields.

You could archive an assembly in the "master" table with a checkbox field, but I think an ObsoleteDate field is a better way to go. If its value is not Null for a record, that version is obsolete, complete with the date it was designated. It may make sense to have Status and StatusDate fields if your business wants to designate other status' for an assembly version, such as Submit (for approval) and Approved, along with the status date. This is not robust enough to have a status history though, but some things are just not worth keeping data on.