Help Designing table structure 4

[dmuroff]

Good day,

Sorry for posting in 2 forums. Thought the question would be more relevant here.

I am in the process of creating a database in Access.

I need some advice on designing the table structure.

I have 10,000 products.
There are 40 product types.
Some fields are the same for all products. (I.E. description, price).
Some fields are unique to the product type.

I am thinking this:

1. Have a table called products with all 10,000 products including description, price, producttypeID and productID.

2. Have a table with producttypeID and ProducttypeDescription.

3. Have 40 tables with ProductID and fields that are unique to that category of product.

ProductID would be the PK in the products table and in the 40 other tables. I would have to have 40 relationships. 1 for between productID and the ID in the 40 tables.

Is this the right way to go about it?

Thanks in advance.

 

[MoLaker]

What is the hierarchy? It seems ProductTypeID is the highest level then ProductID then Products. It appears a ProductTypeID can exist alone, but a ProductID must have a ProductTypeID and a Product must have a ProductID.

If so, then create 3 tables
1. Products including a ProdID column - ProductTypeID column not necessary.
2. ProductID including a ProductTypeID column and a one-to-many relationship with Products.
3. ProductTypeID with a one-to-many relationship with ProductID.

 

[jsteph]

dmuroff,
Just my 2 cents...Don't do the 40 tables thing. What happens then, when you get 41 product types? You add another table to the database and then go around to all the client front-ends to update that, plus all the logic?

I would keep it simple. tblProducts, and tblProductType. The tblProductType is just a lookup holding the description of the product type and maybe some special insttructions on how to ship this type of product, for instance. But, unless there are literally hundreds of fields that one type of product has that the others don't--then just put all the fields in the tblPRoducts.

If what you are talking about is a product with 'components', and one product may have 500 components, while another has 3 components--then you are talking about a completely different animal, and you need a tblComponents.

But if it's just plain 'Products', where one might have a field, say, 'Length', and another, which the attribute 'Length' doesn't apply--then so what--let the field stay empty, or put logic in the form that looks at the producttype and locks it.

I've read all the books on Codd's normalization theory, and it's a good vector with which to direct your design, but in the real world, there are times when you've got to take a line from Tom Cruise in Risky Business and just say "What the f###". Let the fields stay even though they're unused in half the records, unless it's a case similar to the 'components' example above. I would bet massive amounts of money that in the wild, (ie out of the classroom), there does not exist a fully normalized database, so don't worry if you have to break the 3rd Normal Form a time or two.
--Jim

 

[dmuroff]

Hey Jim,

Thanks for the response. You make a few very good points.

I'll have to look at the fields associated with the product types again tomorrow at the office and I'll have to make a decision on how I'm going to do this thing.

I'll post back tomorrow.

Thanks for the help!

Dan

 

[dmuroff]

Just one thing...
Another problem arises with having just 1 products table:
there will be 198 fields in that table.

Can Access handle this many fields?

As well, when it comes to displaying this information in a form or a report is there a way to set the null or blank fields to not appear without leaving any blank spaces on the form or report?

Thanks!

 

[jsteph]

Access can handle up to 255 fields.

Check the structure and nature of these fields, the 'component' example I mentioned might be appropriate here. If the bulk of these fields are of the same data-type and are of similar relation to the product itself, mabye they can be grouped in a separate table.

For example, I have seen Product tables with fields like
ProductID
ProdDescr
ProdPart1
ProdPart1Price
ProdPart2
ProdPart2Price
ProdPart3
ProdPart3Price
ProdPart4
ProdPart4Price
...etc.
Here, a Table 'tblSubParts' or 'tblSubComponents' might have this structure:
ProductID *Key
ComponentSeq *Key
CompPartNum 'separate part# of component
CompPartPrice

Here, this table would have a separate record for ProdPart1,2,3,etc...
Also, it's likely that the components themselves are either stored in a separate components table, or in the Products table but with a 'component' flag.

But...if that's not the case, then Access will handle 255 fields, and you can write code based on what shows on the form.

For example, you would have a 3rd table called, say, tblProdTypeFields
This would have fields:
ProductType
FieldToShow
FieldTabOrder

Now for each product type, you have a record containing that typeid and the fieldname that must show for that type.

Then in the form, you loop through the table and place controls based on their existence in the table for that producttype, and the tab order telling you where to place it. You might have an area with all of the common fields, and then only have the 'variable' fields in this table. Using the .Left, .Top, etc, you can easily and neatly place these controls as you step through records on the form.
--Jim

 

[dmuroff]

Hi Jim,

I don't think the components example applies here.

Here's an example of what the field types might be

Product Type 1 Product Type 2
Watts Watts
Volts Max Temp
Min Temp Circuit
CaseQty CaseType

All 40 products types have a different variation of information. That is one product type may have 3 attributes while another might have 20.

I've tried creating the 40 tables (1 for each producttype) plus 1 table (tblProducts) which displays all 10,000 products with the attributes that all products have in common (i.e. Price, Ordering Code, UPC) but Access does not allow more than 32 indexes on the tblProducts.

It looks like I have no choice but to create one huge table with all 10,000 products and their corresponding 200 field names.

Another fun Monday!

Thanks for the help Jim!

Dan

 

[lespaul]

Then would something like this work:

tblProducts
ProductID
ProductName


tblAttributes
AttributeID
AttributeDescription

tblProductAttributes
ProductID
AttributeID
AttributeInformation


tblProduct
ProductID ProductName
1 Something

tblAttributes
AttributeID AttributeDescription
1 Watts
2 Max Temp
3 Min Temp

tblProductAttributes
ProductID AttributeID AttributeDescription
1 1 100
1 2 300 degrees
1 3 -300 degrees


Then in the tblProductAttributes table you could have 1 or 1000 attributes for each Product.





Leslie

 

[jsteph]

dmuroff,
And that is a good, accepted way--having all 200 fields even though in any given case that product may only use a fraction of the fields. Your coding and future additions will be much easier, and disk space is cheap.
--Jim

 

[dmuroff]

Yep looks like that's the way that I'm going to have to do things.

Is there a sample code available to show fields that are relevant to that particular product? (I.E. do not show the blank fields for any given product)

Thanks

 

[lespaul]

Is there something in particular about my solution that won't work?

Is there a sample code available to show fields that are relevant to that particular product? (I.E. do not show the blank fields for any given product)

The only way to do this is to have a query for each product type and only select the fields that are relevant for that product. I would strongly suggest that you look at the model I provided. The queries that you are going to have to design to eliminate the blank fields for each product are going to be monsterous!!!

WHERE FIELD1 <> '' AND FIELD3 <> '' AND FIELD7 <> '' AND FIELD56 <> '' AND FIELD78 <> '' AND FIELD89 <> '' AND FIELD100<> '' ETC ETC ETC

If you use my structure you would ONLY get the attributes that apply to that product. This query would return:

SELECT * FROM Product INNER JOIN ProductAttributes ON Product.ProductID = ProductAttributes.ProductID INNER JOIN Attributes on ProductAttributes.AttributeID = Attributes.AttributeID

1 Something Watts 100
1 Something Max Temp 300 degrees
1 Something Min Temp -300 degrees



Leslie

 

[dmuroff]

Good Tip Leslie,

I will take a look at what I have and will have to decide on something soon.

I will post how I decided to undertake this problem.

Thanks again Jim and Leslie for your help!

 

[jsteph]

lespaul,
I don't see where you'd need to do what you suggest in my example:
WHERE Fieldx <>"" and Fieldxx <> "" etc, etc.

This is not what I'm getting at at all. The field being blank has nothing to do with the query. The query--the source for the form--returns ALL fields regardless.

The code i alluded to in the Current event of the form simply reads the 3rd table and decides which ones to show.
--Jim

 

[jsteph]

lespaul,
Your example is quite similar what I had suggested in my 'components' example. This is all valid and good--but my point was simply that this may be too much 'normalization' and complexity for this system. 10,000 products is not really that huge a table, and to carry 200 fields for that amount of products is, in my opinion, worth it to reduce the complexity of the attributes/components table.

A mid-level solution I suggested was the 3rd table that lists attributes (fields required) at the Product Type level--not the Product leve. This has the advantages of being smaller, but more importantly it is optional--ie, you can still select the stuff you need in an ad-hoc query without needing the complexity of the 3rd table--this table's purpose is primarily for adjusting the form's display.
--Jim

 

[lespaul]

Then how do you answer this question?

Is there a sample code available to show fields that are relevant to that particular product? (I.E. do not show the blank fields for any given product)

not on a form, like your suggestion above, but in a query?





Leslie

 

[jsteph]

I wouldn't do it in a query--that's an ad-hoc device that isn't for general user interface. However, if I were to do it for a query--the same general idea can be used in a query--you generate the SQL based on the table holding the ProductType/FieldsToShow.

So if (and it's a big and unlikely 'if') the users or the developer decided that queries were to be the front-end viewport of choice, then the same logic could generate the query based on the ProductType.
--J

 

[mbuell]

Well, well. This seems to have gotten into a very complicated series of answers.

I don't know if I can help or not, but here is my take:

Choice 1: products table with <product type id> field, <product type> tables (many)
Choice 2: products table with <product id>, intermediate <product id> to <product type id> table, <product type> tables
Choice 3: single flat table
Choice 4: products table with <product id>, intermediate <product id> to <product type> table, <product type> field characterization table(s), and single <product type> table containing type data.

All are valid approaches depending on the situation. I would rule out the flat table (3) for two reasons - the complexity of recalling only the specific data required, and because we are after all using a relational dbms. I would rule out (4) because, while it would be a great approach if you need a really robust and scalable deeply programmed approach, in a simpler business type situation it would be too labor/expertise intensive.

That leaves me either with the wrong assumptions, or with choices (1) and (2). Of these two I like (2) because it offers many product types for any given product. That may NOT be the case, in which case (1) wins.

Here's a problem with Choice 1. When you design a report to show Category, Product, Description details, the report has to figure out what to call to show each Category Detail. This could be worked with by using numbers as field names in each Category Detail table, then using a translation table to store Category Detail descriptions. Personally, I would design a function to list my current table entries and read them into an array to use for the report. That way, when my report or form query needs to know exactly which categories or details it is going to be managing, I call the function and bingo, I can set up my SQL string to what I need at that moment. If you are not up to designing such a function then you may be stuck with manually adding categories fields to a report and distributing front-ends. Or designing a subreport for each category (I've done this too) and setting the report to call the subreport based on the category name. I'm using report/form pretty much interchangeably. There are some differences, but that is way too much detail for now.

I have used approaches like (1) and (2) many times, and find building interfaces, reports, etc. not to be compromised by using this solution. Each approach requires slightly different techniques to build queries, reports, etc. Choice (1) does require someone who can work in Access if a new category is added. It's not a very big deal to design forms and reports that just look up the category IDs to go find data. So front-end redesign just because you add a category table is not necessary, if you can get your queries to use a list of the current categories using code or macros. That's all.
Ok, now - shields up -- <Flame On> ;-)

Like to hear what you come up with.

Mark
<O>
_|_

 

[dmuroff]

Hi Mark,

I have chosen your Choice #1.

Now I have received a series (30) of dump files (1 for every product type). I need to find some way to get all the damn records from each of these tables into 1 single products table.

This would appear to be easy but the problem is that each table has different fields. Each table has 5 fields which are exactly the same (ProductID,Price,Description, Producttype, Cost). The other field names are specific to the producttype.

For example:
1) Producttype 1 and 23 might have a field named 'volts' while the other tables do not.
2) Producttype 3 might have 'length' while the other tables do not.

I have tried messing around w/ the queries but have still not found a successful way to query all 30 tables.

Thanks for your help on the design part but can you think of any way to get all the tables into 1?

Thanks in advance!

 

[Elysium]

To throw a curve in this discussion, what if dmuroff was asked to have this project become the basis for an inventory control system integrated with a POS? How do you manage a 41st product without having to make a table in Access with the product's description and then re-establishing relationships?

Randy

 

[mbuell]

Elysium: see bottom of post for my swing at the curve.

dmuroff: Ok, now we're cooking with gas. Answer a question - is your download static, or will the products be downloaded regularly with regular (in terms of time) changes? If relatively static, and we can create a new big product table, then simple append queries will do it for the original import. Don't bother automating if you are only doing once or infrequently, UNLESS you've got to turn this over to an end-user to do. Regardless, first append the similar data. Now, we've got all that stuff in one big table, and we have to worry about the details. Decide on your structure. If we do the multiple tables, create a list of import file/table names, and if necessary a list of corresponding table names. Eg. crunchies.csv = tbl_crunchies, etc. Either open the 40 or so import datasets and cut and paste to new tables, or design a routine to read your list and do it for you. Designing a routine will take longer at first, but will save time if task becomes a regular thing, and will be required eventually if end-users have to manage such data transfers.

Now, if you really need to keep all your products in different tables, and NOT create a master product table, then answer this question: is ProductID always unique - between ALL tables? Can't have ANY dupes. If yes, Create a master list of productID, and link all tables as children to master list. Remember, we are NOT trying to show all description fields yet!! Just all the like fields. I don't like this method, btw.

Now do you have to see all the products, with all their descriptions in one table view? I don't think so. If you tell me yes, ask yourself why. See if there is a simpler way to achieve input/output than such a table. If the answer is still yes, this can be done with a crosstab view query, but that will be more than a bit tricky. I would not care to make that work straightaway. If I had to do that, and I have had to do similar things, I would pull everything into an intermediate flat table. This is NEVER a table that would accept input. It is ONLY for output. We will have to manage input some other way. After I have the intermediate table, I can pull up views I could not otherwise do. I can automate a query or series of queries to accept a little input from me for period or other parameters, and then let them rebuild the table. This table has no other function other than providing output, so no data is ever destroyed, and it can be rebuilt infinite times.

Sorry for the full book here, but I can't say it quicker.

Now for the curve: thwack! It's a hit! But where will it go?
If indeed, this db was created and then requested to function in a larger capacity - I would know that that it was never designed to do this, and that it would be just as efficient to build a new db and then import the data. I would then tell my client or boss or whathaveyou, what I think was needed to make such a change. We've got some structures we can keep if we like, but if we are going to increase the functionality in such major ways as POS systems, we are going to go to SQL-server or something similar. We are not going to do it "on the cheap". This means that there is either time or funding to create more robust alternatives. One of my assumptions is that the robust, but initially expensive approach, is NOT justified or desired. Regardless, this effort can be grown into a larger work. Migrating from (1) to (4), where there is one detail table, and a key to the field characteristics, should be quite doable. It will take more work in toto, but often in real life business this is the way one must move forward.


Mark
<O>
_|_