Table for Products

[tmyers]

I am rebuilding one of the first Access databases I made several years ago and have run into a problem with correctly normalizing my product table.
Here is what I have so far:

I have it this way as a product can have many prices due to different projects, quantities, end user etc so I felt the actual pricing needed to be a many-to-many join table. The problem I foresee later on when importing quotes from the vendor is slight variances in the part numbers, such as using spaces instead of "-" or not having spaces at all etc. This wasn't an issue when, in the prior iteration, this was all one table and didn't matter. Am I going about this correctly or am I overthinking it again?

For posterity sake, here is my entire schema as it current stands and will take all the advice/abuse you all can give

 

[jdraw]

Regarding variations in part numbers, you could pre-process/edit these to remove spaces and/or "-". The key is to get some common scheme to eliminate the variation/uncertainty.
Perhaps you could show some samples which exhibit this issue. It would also be helpful if readers knew more about your business processes.

 

[tmyers]

I have done went and confused myself. I may just be overthinking this way too hard.

The problem I am worried about is a part number coming across 3 different ways. Such as IBL 15L WD LP740, or IBL-15L-WD-LP740, or IBL15LWDL70.

 

[plog]

You build a mapping table. You have a text field for the part number that comes from vendors and it goes to whatever ID number your database uses for that part.

Couple notes:
1. Avoid synonyms. No where do I see 'Part' in your schema. My guess is its a synonym for product, but I'm not sure. Eliminate that uncertainty for me and for everyone who is new to your organization and will have to use this database by using only 1 term for a concept. Makes communciation a whole lot more effective.

2. You probably don't need JobComplete field. My guess is you can simply look at CompleteDate and determine logically if a job is complete, right? No need to duplicate values.

3. You need a cost table. You are storing data in field names--FixtureCost, BuyCost, LampCost, etc. Instead you need a CostType field where all those prefixes go along with a simple Cost field that details the numeric cost.

 

[tmyers]

You are correct with the naming Plog and I apologize for it. Its one of my worst habits when it comes to these things sadly.

I had used the JobComplete and CompleteDate for two different things. JobComplete was shown on my forms to flag a job as done and then I had some code that time stamped it in the CompleteDate field. The checkbox was for the users and the date field was for queries to track average turn around time and such.

For your number 3, I am afraid I dont follow. Are you saying rather than have the tblProductDetails, just change it to say, tblProductCost and then name accordingly so it is more straight forward? Or do you mean something completely different?

 

[plog]

#2->You can still have a checkbox for JobComplete that doesn't actually save to a Yes/No field. Instead code fires and puts your value in CompleteDate, or takes it out if they uncheck it.

#3->A new seperate table with a 1 to many from tblProductDetails. 1 ProductDetail to many ProductDetailCost. Structured like so:

tblProductDetailCosts
pdc_ID, autonumber, primary key
ID_ProductDetail, number, foreign key back to tblProductDetails
pdc_Type, short text, this will hold the type of cost which is now a prefix of field names in tblProductDetails (Fixture, Lamp, etc.)
pdc_Cost, number, this will hold the actual value in all those cost fields.

So instead of X fields for cost in tblProductDetails you add X records to tblProductDetailCosts.

 

[tmyers]

I see. I will make those changes and report back once I do them to make sure I understood what you said. It likely wont be today however as I am being pulled away to another project. Thanks plog!

 

[jdraw]

Just came back to the thread. I agree with plog as approach.

part number coming across 3 different ways.

You have to make a decision on how you will "handle this issue" for consistent processing and data integrity.

 

[Pat Hartman]

We buy for one price and sell for another. I don't see that in your schema. Also, you will normally have YOUR part number and the vendor's part number and different vendors can have different part numbers for the same item. So, you have a part table with with the generic attributes, you have including YOUR part number and your default sale price. Then you have a child table that has the Vendor and the vendor's part number along with his price with a date range because you're going to probably want history on this. You might break this into two tables. VendorPart and VendorPartPrice

Then for a job, you link to YOUR PartID and either select the default price or a special price for a particular job. I always store sales price rather than relying on linking to the part table to get it because it makes future processing easier.

 

[tmyers]

For the differences in how the part number is presented, I believe I have decided all strings will go through a process to remove all spaces and hyphens to make them all one solid string as this is how we prefer it and it would be significantly harder to do it any other way.

Pat - For this application, we get quotes for every job from the vendors and pricing isn't always consistent from one job to the next. It is generally close but not always the same. Our sales price changes constantly depending on the quantity being quoted as well as the cost of the job as a total. Obviously we would give a lower price if quoting 500 of a fixture vs 10 but would also give a better price for those 10 in a job that is worth $500k vs a job that is worth $20k. Its a very hand tailored process, at least at the moment. But before I got sidetracked, I have not yet put in a table to deal with the sale side as I had not yet figured out how to handle it. I assume it would be kind of like Plogs suggested table but would hold the markup %'s and such.

 

[tmyers]

Ok, here is how I now have it laid out:

I did sale the same as cost but there are technically only two options (% Markup or hard entered price) as well as comments (internal vs external). Does this look more correct?

Edit:
I just noticed myself failing on naming again. All fields in tblFixtureNote have been changed from "comment" to "note" for consistency, same for the foreign key in tblFixtureQty.

I also added another table, tblFixtureType which only has two fields, the autonumber and a short text field to store the designation for that fixture for that particular job.

 

[Pat Hartman]

For the differences in how the part number is presented, I believe I have decided all strings will go through a process to remove all spaces and hyphens to make them all one solid string as this is how we prefer it and it would be significantly harder to do it any other way.

Typically, when you place an order with acme, they want their own part number on the order. They have no clue what YOUR part number is. NOR do they know how you reformated their part number. So, sorry, you need to keep their part number.

Not keeping inventory affects how you store everything. But, you still need a part table that contains YOUR information. Then you need a child table that contains the vendor part number because that is how you will ask the vendor for a quote.

#11 is not correct since it is not related to a job. If you don't keep inventory, everything is dependent on a job. I also don't understand why Cost, sale, and note are 1-m with tblFixtureQty

 

[tmyers]

I am having a really hard time trying to describe the process and I am sorry for that. Let me preface with this may end up long winded but my overall goal is to (re)create a app that we can use to quote/bid lighting packages for construction. This was the first Access app I ever created and while it works, its a hot mess and is at the point where I cant make improvements without rebuilding it from the ground up.

Attached is an example of a quote from one of our main vendors that I am working with. This particular quote is lot priced vs itemized, so there is was no pricing provided per line from the vendor, just a total for the entire bill of material. Why I am having such a hard time with this is every quote is can be completely different or nearly identical but every quote is very specific to that particular job but I need everything from it to create a finished quote on our end. Not all vendors quotes are structured the same, but they all contain more or less the same information that we need.

The attached is a good example (minus the missing pricing) as it has exactly what we need without a bunch of fluff. My existing method was to just append this entire thing to a table and go about my business, but that method is horribly wrong as I understand it and also makes the data hard to report on. On top of this, we need to add the extras that are required for a complete price such as the freight and cost of light bulbs (if applicable) and add our markup to it, which can be different from job to job for the same product.

If UPC's were provided this would probably be a lot easier, but in the years I have worked with these vendors, even if a quote has the UPC field, most are blank as the products likely dont have one anyways.

Honestly, I wish my work would just invest in proven software like Oasis or Accubid and save me all this trouble.

 

[Pat Hartman]

The only problem I see is lines 14,23,25,50,51,52. Some seem to be continuation lines. Others stand alone. Line 62 seems to be a group item that may or may not have a price. Do you need them?

Is Type what you send the vendor for a quote? If those are standard, then you don't need to keep the vendor's catalog number unless you want to.

I've seen far worse layouts from my bank as they download transactions for the month

This version seems to have what you need to join back to your request. You enter the JobID and join on the type field to update the estimate for a particular vendor.

 

[tmyers]

Lines like 14 are what would probably be known as a kit. The top most item (in regards to 14) is the LED engine (the actual light) and the bottom part (the JPDZ4RDNCMF) is the housing that it goes in. There are likely a multitude of different LED engines that could go into a variety of housings, but that is the combination for this particular job to make a complete unit. Line 62 is not a physical product but a service that does need kept as they can cost thousands, or even tens of thousands of dollars depending on the job.

Type is very specific to the job. These are the designations that the engineer for the project chose for that particular light fixture. They have no rhyme or reason from job to job and are never related to anything else really. They are just ways to uniquely identify that given product for that given job. The real fun kicker is that type may have 5 different approved products, so 5 completely different products will have the same type designation tied to them.

Attached is an example of a fixture schedule from construction documents. This snip isnt for the same job as the quote I attached in #13, but it shows where certain types can have multiple possible products being quoted.

And I can believe bank processes being absolutely awful.

 

[CarlettoFed]

First of all you should explain the meaning of each column and which field in your database it can be associated with.
If you attached an example file of your database with a minimum of data entered for a couple of offers following a request from two contractors, it could help us understand better.

 

[Pat Hartman]

How do the engineers tell you what to get quotes for? Using a detailed specification seems to be inefficient for common, off-the-shelf parts. There must be some way that what the engineers spec can be translated into a RFQ and then the resulting quote from the vendor mapped back to your RFQ. We can't look at this with individual parts. There are at least THREE parts to this problem.

I've done lots of manufacturing applications. They aren't that hard.

 

[tmyers]

Carletto - Sure thing!

In reference to the example quote in #13, "Type" is the unique identified for that given product for that given job. Types can repeat from job to job, but the product the type is identifying can be completely different as they don't relate to each other. In my current format, they would be kept in tblFixtureType and is why it is its own table.

Qty - Self explanatory. Simply the amount that is needed for that given type/product for that given job. This is my many to many table tblFixtureQty. I did it this way as this is how I handled it previously, so it made sense but it likely isn't "correct".

Manufacturer & Catalog # - These are the constants and go into tblFixture. This is what I really want to keep track of and have every entry be unique. Due to the sheer amount of manufacturers and their extensive catalogs, I except this table to get quite large as a single product can be configured in so many different ways (lumen output, input voltage, dimming type, lens type etc). Within this table, LampType is used to store if the fixture is natively LED or if it isn't, what lamps/bulbs it actually takes (A19, PAR36, CFL, T8, T12 etc) Then NumberOfLamps is how many of them is needed to complete the fixture, which if the fixture uses LED boards, would be 0, which will be the normal for this field as lamps are not used much anymore in commercial spaces.

Line Comment - This would go into tblFixtureNote as the comment would be specific to that product for that job. Unfortunately, descriptions also make their way into this field on the quotes and would need moved to the [Description] field in tblFixture.

Unit $ - The price per each of the given product. Goes in tblFixtureCost and a given product could have one entry in this table, or multiple depending on the job. A single product could have the cost of the fixture itself, the cost of the lamps, the cost of shipping/freight and a host of other misc fees that we would roll into it to get its "true" cost.

Ext $ - Not stored as it is not needed and can be calculated later.

All vendors quotes, regardless of their formatting, have the same fields (some move the comments to below the product line, but they are functionally the same) and all are needed for us to provide an accurate and complete job quote at the end.

For reference purposes, here is the product table as it exist in the current database. Vendor quotes are imported and go through a few temp tables before just being dumped into this table line for line regardless of if the product already exist in it.

Back then, I didn't know any better and just kept cramming everything into this single table. It works for what I wanted it to, but it's wrong and not very flexible, which is why I am now trying to improve it and make it "right".

Pat - We are told what to quote based off the fixture schedule like the picture in #15. Every (well almost every) set of construction documents that involve electrical will have this schedule. It list out the approved manufacturers that can be used on the job, the type designations assigned to them and the description of what the engineer wants (which trumps the specified part number in the event the fixture specified via the part number doesnt match the description).
Since most jobs will specify multiple approved manufacturers, we will have to get quotes from multiple vendors to put together a complete quote. We can also sometimes put together multiple quotes for the same job using different manufacturers. Quotes also can be a mix and match from multiple vendors as one vendor can't always provide product for the entire job due to not having their product specified.


That was very long winded, but I am trying to confer as much info as I can. I can't provide the DB yet as I don't have enough of it build yet to really start putting in dummy data for an example but am trying to get there.

Edit:
Trying to enter dummy data, I already see that how I have done the tables will not work.

 

[tmyers]

Ok, I have reordered it a bit.

My logic is a job can have many fixtures which can have many different sell prices and quantities. A fixture can have many cost, notes and types. I am still not sure if I have the many to many correct though.

 

[Pat Hartman]

Please give us an example of what identifier you get from the engineer when they want to order an off the shelf part. Do you use that identifier when you send the RFQ to the vendor or do you use the identifier to look into your parts table and find all the vendors who supply that identifier and then send the RFQ's with the specific vendor's catalog number.