Table structure analysis please

[Mike Krailo]

I'm posting table structure here to confirm or deny some of the issues that are present. I'll just posts the pics and let you look at them. This is for a company that installs window treatments and the owner has been trying to transfer from using spreadsheets to access. Of course there are lots of normalization issues.

The CustomerTable has a field JobName which looks to be an obvious issue but my client is amendment about keeping that field in the CustomerTable. It usually is the customers last name or the abbreviated name of the company and for some reason he calls it JobName. There is also a LeadSource field in there which is is defined as text but is being used as numerical Fk to LeadSourceTable (There are other fields like this throughout the database). Not sure the gift card thing should be in there either.

On the JobTable, there is a Parts field which could possibly be normalized from an actual Parts table which does not currently exist.
also on JobTable, there is TotalSale which stores a calculated value. Essentially, this appears to be fields from a spreadsheet that was inserted into the table.

JobTable has Street, City, ST, Zip of the location where the Job is to be done at. Should be a Fk to either CustomerTable or JobSiteTable. I'm sure there are more problems, but these are the one's that stuck out.

 

[The_Doc_Man]

A few things jump out at me.

In your Job Table the comments for the fields indicate something about invoicing. If you have that much to keep in the job table, it doesn't belong there. You have enough for a separate table. If you EVER have to do "split" invoicing then with that structure you can't. And it appears that you have what SHOULD be invoicing line items in a separate table, but you have them in one table with a single record per job.

Your Labor Table has fields qualified by the number 2 that correspond to other fields in the same table that don't have the qualifier. This suspiciously resembles a "repeating group" which is one of the signs of need for normalization.

Identifying other normalization hot spots requires more knowledge about how your business actually works and I can't give you guidelines there.

 

[Mike Krailo]

I'm working on getting more details on the business model as this is not my business. I see what your saying with the Labor Table, my guess is it is a carry over from a spreadsheet. Thanks for pointing that out.

I think I see what your getting at with the Invoicing as there ARE separate line items. Those line items I believe are in the ItemsTable per JobNumber. I'm thinking that he wants to store the calculated values in the job record because that's the way it looked like on his spreadsheet.

 

[mike60smart]

Hi Mike

Can you upload the actual database in zipped format or the actual Excel sheet with Confidential data removed?

 

[Deleted member 147267]

Without knowing the business process, we can only guess. Comments:
- if you can give gift card to customer more than once, it doesn't belong there
- job name doesn't belong in customers. What happens when you do another job for them, add all the customer details again for a new job?
- JobID, PO, tax, etc. doesn't belong in items table if that is a table for items in stock or typically If not, names maybe could be more descriptive (e.g. JobItems pretty much answers the question - at least for me).
- IMO, it's redundant to have "table" at the end of every table name.
- parts in JobTable allows you 1 part per job, or you repeat job data for every part??
- does a job have a color?

That's enough for now, but I think I see lots more. Sorry but I don't think you understand normalization completely yet. I treat it as an entity/attribute relationship when trying to decide where to put things. The entity is a "thing" with characteristics (attributes) that define/describe the entity but each attribute can only be in the table once. So a customer table does not contain job related fields because the job does not describe, or is not an attribute of, the customer.

IMO what would be needed most in order to get accurate help is an understanding of the business process; a high level description of what goes on.

 

[Mike Krailo]

In a nut shell, a Customer wants to purchase custom window treatments and looks at the various designs and decides on choice of colors, fabric, cornices, etc... If it's a new customer, their name/organization/company info is entered into the system. Sometimes a customer is entered based off of a referral or what he calls a LeadSource. So that name is also entered in. At this point the customer has not committed to anything yet so they are just classified as a Lead and so the LeadDate is filled in to show when they first became interested in purchasing window treatments. The JobType code is also filled out (residential, commercial, and about 12 other job type codes).

Now about the JobName, the client claims that this name never changes from job to job and is why it's in the customer table, usually is just the last name of the person or company name. So even though there is no actual job yet, they have already been given this JobName. I think it's just a matter of semantics as I would not have done it this way but they have been doing it this way for years, hence the lack of desire to change it.

A Lead is defined by the fact that there is currently no open job date yet. Once a customer makes all their choices for types and quantity of window treatments, a bid for doing the job is quoted to the customer and then they can accept or negotiate a different price. Once an agreement is reached, the Open Job date is filled in and actual bid is filled in, a promised date is given and filled in, and most importantly a unique JobNumber is assigned.

Now each Job has items that must be purchased from various vendors and at three different possible tax rates! That's why there is a TaxType field in the Items table (Fk to TaxType table). This part of the database is working fine. When a new item is added, the very first thing they do is decide what tax category that item is in so that the proper tax can calculated later. The details of that item are filled in. More items are added as necessary using the same JobNumber and finally the Purchase Order is created via PO button on the form.

While they are waiting for the orders to fill, they do all the scheduling for the job or any prefabrication work. The orders come in and on the date of install the assigned individuals install the window treatments and then the DateCompleted gets filled in and they await payment by the customer.

Micron, I'm not sure about the gift card part yet. And I totally agree about the redundant naming with Table at the end of each name. This is the work of the owner of the company, not me. It's his first attempt at a database. The exception is that I combined his original three item tables into one and added the taxtype to solve a particular problem that he was having.

I also agree with the parts in the job table (i'll have to ask him about that). The thing with the color is usually all the window treatments have a theme color or colors to them. So it is basically a specific notes field describing the colors a customer is interested in for this particular job.

 

[Pat Hartman]

I think a better name for JobName might be CustShortName and as long as it stays in the customer table and not in the job table, it is fine.

Company contacts change over time. If you don't care about previous contacts, then having the contact in the CustomerTable is fine along with the GiftCard flag (which I would rename CanSendGiftCard). However GiftCardSent should probably be a date and better still, should be in a separate table. In this case, it would be a history table and so I would also include CompanyContact so you know who you sent the gift card to.

The other advantage of keeping contacts in a separate table is if you log communications, you can keep track of who you talked to.

DoNotContact should probably be a date rather than Y/N. Using dates rather than flags gives you more information. If the flag was set 10 years ago, maybe there's new management and it is OK to call on them again.

What other types of client besides Commercial exist? This might be better as a combo.

Fields with numeric suffixes should be placed in a separate table. Why is there only one ext field but two phone numbers? What is Detail1,2,3?

The item table is confusing. It looks like there are no standard items. Every item is special ordered. Is that the case? It also looks like the ItemTable is a mishmash of item information and order information. Perhaps using a more standard schema would help. Keep an item table with costs, an Order table to define the order, and an Order Items table to connect any item to an Order and add customization details such as color. Sales tax can be tricky. You have to collect tax for the state where your business is located or for the state where the product will be delivered. I am not an expert. I just know that the rules are complex and don't belong in an item table. Some non-profit entities don't need to pay sales tax. That should be recorded on the customer record.

I'm guessing that the Labor table is all about installation cost. Here again you have repeating groups

Since there can be multiple payments for a job, the paidInFull flag (date) should probably go in the Job table. Having this flag at all is a violation of normal rules since you can sum the payments and compare them to the billed amount to determine what has been paid. If you do use the PaidInFull flag(date) on the Job, you have to make sure that the code that runs in the Payment form properly updates this field. You should also add a type field to the Payment table so you can identify which items are deposits.

The ShipToTable should include CompanyID, NOT details from the company record. Remove Comany and Conact and replace with CustID. If you are going with a separate address table, the best solution is to put all addresses in the table, not just ship to. In the CustomerTable, you could have an BillingAddressID which points to a record in the AddressTable. In the JobTable, you would then have a ShipToAddressID which points to the AddressTable so that you can use one address for both billing an d shipping.

If Vendors can be customers, you might consider using a single EntityTable and keeping a related table that includes role which you use when you want to pull up "customers" or "vendors". If there is little overlap, this isn't a big deal. It just reduces the tables and forms you need to create.

 

[Mike Krailo]

What other types of client besides Commercial exist? This might be better as a combo. There are just two broad categories of clients, commercial & residential.

The item table is confusing. It looks like there are no standard items. Every item is special ordered. Is that the case? Yes, that is essentially the case and the reason the standard schema didn't work. There will always be something custom ordered from the vendors in the VendorTable to produce the final products that ultimately get installed. As far as the tax, there are only three possible taxes: NoTaxItem, SalesTaxItem, UseTaxItem. The decision on which one is used comes from the user and as soon as they click add new item button, a three button choice menu appears to tag that item as being in one of those three categories. Then the rest of the info is filled in for the item to be ordered. So the business owner and manager are smart enough to know which is which. They used to have three different spreadsheets that corresponded to the three categories of tax items.

Since there can be multiple payments for a job, the paidInFull flag (date) should probably go in the Job table. Having this flag at all is a violation of normal rules since you can sum the payments and compare them to the billed amount to determine what has been paid. If you do use the PaidInFull flag(date) on the Job, you have to make sure that the code that runs in the Payment form properly updates this field. You should also add a type field to the Payment table so you can identify which items are deposits. The PaymentTable is definitely a problem and I agree on all your points. If you seen his data, you would see "check numbers", "????", "PayPal", cash, or null values in the CheckNum field so obviously this is not a good structure.

The ShipToTable should include CompanyID, NOT details from the company record. Remove Comany and Conact and replace with CustID. If you are going with a separate address table, the best solution is to put all addresses in the table, not just ship to. In the CustomerTable, you could have an BillingAddressID which points to a record in the AddressTable. In the JobTable, you would then have a ShipToAddressID which points to the AddressTable so that you can use one address for both billing an d shipping. I can't believe I didn't see this. That's a great observation.

Very good analysis Pat. Thank you so much for your advice.

 

[Dreamweaver]

I think you need to add a number of addition tables but on my tab at moment but what are all the conversation values could they be added to a preferences table if the same values are reused.

 

[Mike Krailo]

Mick, not sure what conversation values you are referring to. Unless you meant conversion and were looking at this:

Those are the values of calculated fields on the subform in hidden controls used only to get the separate sums for the different tax categories of items per JobNumber. They are also used on the main form for other sums on the JobData tab. If there is a better way to do that, I'm all ears. If I can get a stripped down version of the DB uploaded, you would see what I mean. That was one of initial problems I had to solve that was not working at all prior to to working on his database. It now does what he was trying to do.

BTW, love all your database examples and work in general.

 

[Pat Hartman]

I would take a longer look at using a more standard Order, OrderDetail, Item schema. It seems like every time something is ordered, there is a lot of data that is entered that might not need to be. I'd need to see more of your product line to have a better idea. If you can post a link to the company website, I'll take a look. Or if you don't want to post it publicly, send it to me via PM.

 

[Pat Hartman]

I took a look at the website and I think you should go with a more standard schema. You should define all the items in an item table including the vendor you purchase them from. You can add a category field that will make choosing an item easier. It will work similar to a menu. Pick window coverings and the next dropdown shows only types of window coverings. I wouldn't go more than two levels. You could even use one but maintaining the list would be more difficult. You could add a couple of fields that indicate required values for certain items. For example, height, width, color, drawstringPosition. Technically this should be a second table but it might be easier for you to make this part "flat". The downside of making the design "flat" is that if you add a new product type that requires an attribute that you haven't already defined, you will need to change a lot of stuff to get the new attribute in. So, in the item table where this is defined, you would have a series of Y/N fields and you would check each required option. Then when you are building the orderdetails, your code can ensure that all the attributes are present. You can't order blinds without a width for example. You can go even further if you want to but doing so would require that child table both below Items for the definitions and also below OrderDetails for the customer values. This would give you the ability to have four entries for honeycomb shades. Min 12 Max 24 with a price. Min 24.1 Max 48 with a price, Min 48.1 Max 96 with a price, Min 96.1 Max 128 with a price. When the order details is created, one of the four options is selected and the exact measurement is entered. Your code can validate that the measurement falls between the min and max for that item. This prevents data entry errors and will reduce your return costs so it should be an easy sell to the customer because most companies that provide semi-custom products like this will offer to re-make the item if it is incorrect.

Using the child table to handle your customization options is slightly more complicated initially but infinitely more flexible which is the whole point of normalizing a schema to begin with. When an item is selected in the OrderDetails, the app would look in the ItemAttributes table for the attributes required and insert a row for each attribute found in ItemAttributes. So it would insert Width, Height, Color, Style, whatever, all with the actual values empty or you could fill them with defaults if there are defaults defined. I would probably avoid defaults just because it makes it easy for people to gloss over them blindly. They are less likely to make a mistake if they have to type something and you can validate that the "something" is in a valid range.

If you create your Order and OrderDetails and now OrderDetailsAttributes correctly, you can use this data to actually place orders with the vendor so it will ultimately save you data entry and eliminate data transfer errors. Again, this should make your customer very happy.

 

[Dreamweaver]

Those are the values of calculated fields on the subform in hidden controls used only to get the separate sums for the different tax categories of items per JobNumber. They are also used on the main form for other sums on the JobData tab. If there is a better way to do that, I'm all ears. If I can get a stripped down version of the DB uploaded, you would see what I mean. That was one of initial problems I had to solve that was not working at all prior to to working on his database. It now does what he was trying to do.

BTW, love all your database examples and work in general.

Thanks for the kind words

You could look at an older version on northwind that may help I know @Uncle Gizmo has a good copy online hopefully he'll post the link

regards

mick

 

[Mike Krailo]

Pat, that might actually work. I will run it by the owner and see if he will go for this great idea.

 

[Pat Hartman]

Good luck. Sell the "pays for itself by reducing rework" idea.

 

[Uncle Gizmo]

You could look at an older version on northwind

Thanks Mick.

Both versions of the northwind databases are downloadable from this link here:-

Northwind - ALL Versions

This download contains the two versions of the Microsoft Northwind database that I know about. If there are any other versions, please let me know and I will amend this download accordingly. More Info Here:- https://www.niftyaccess.com/northwind-downloads/Older:-Newer:-

gum.co

There is also a video at that link that shows you how to to download the Northwind sample database through your Microsoft Access templates Page.