setting up tables

[btappan]

I am confusing myself more and more as i read on normalization and queries and everything else. I am trying to create a database for parts that our company sells and am having trouble deciding how to lay out the tables. I figure I should use the part number for the primary key as it will not repeat. from there though, i have a problem, each type of part has different fields nessesscary to decribe its attributes. for example, one part number may be for a box and its describing fields might be brand, width, length,load rating, and height. but another part number might be for a circuit breaker which would have completley different describing fields like brand, amperage rating, voltage rating, connection type, and trip type. Should i create 1 table for everything even though many fields would be blank depending on which type of item it was or should i create seperate table for each type of item with only the required fields to decribe that item?

 

[boblarson]

This is the way I would set it up.

Table One - tblParts
PartID - autonumber (primary key)
PartNumber - Text (the actual part number)
PartDescription - Text

Table Two - tblPartAttributes
PartAttributeID - autonumber (primary key)
PartID - Long Integer (foreign key from Parts Table)
AttributeID - Long Integer (foreign key from Attributes lookup table)
AttributeDesc - Text (the attribute value)

Table Three - tlkpAttributes
AttributeID - autonumber (primary key)
AttributeDescription - Text (actual words like "width", "load rating", etc.)

 

[btappan]

So in table one, the "PartDescription - Text" would bea short desc. like box, or circuit breaker? in table two the "PartID - Long Integer (foreign key from Parts Table)" would be the actual part number or the auto number next to it? for looking up parts by their decription would there just be something that sifts through the decription text for the individual attributes? Please explain a little bit of logic to this so i understand it better. Why not just a table with a part number for a primary key and then a long text decription with actual words like "width", "load rating", etc. next to it? sorry this logic is all new to me. =)

 

[EdFred]

I have a product table similar to what you have, and just leave certain fields blank.

 

[boblarson]

I have a product table similar to what you have, and just leave certain fields blank.

That isn't a good idea when you have such varied items as it is really inefficient and at times non-normalized and hard to gather intelligence on.

I'm enclosing a sample that I had created a couple of weeks ago for another poster here. It dealt with employees and attributes like birthdate, preferences, etc. but hopefully you can get the idea.

It uses a form to display the primary data and then a subform to display (and enter) the other attributes.

 

[The_Doc_Man]

OK, let me chime in on "motivation" for the layout Bob suggested (which, by the way, resembles what I did for my CD database.)

You have two options to consider. First, simplicity. Second, efficiency. It is sad but true that for the problem you describe, these two factors are at least somewhat in opposition. Why? Because your problem doesn't really ALLOW the simplicity that is the ideal and goal of every good designer. You have already stated (not in so few or so exact words) that you have a wild mix of attributes that you might need to describe these things.

The issue for using a table with lots of fields works ok if you can warp or otherwise map your field names to cover everything. You will have a few odd spots, a few blanks, but it is workable. However, this is a matter of degree, not kind. From your description, you would be BADLY warping the meanings of things, somewhat akin to the phrase, "bang on it to make it fit." When such an operation is done, your database will LOOK like it has been hammered.

The REAL issue is descriptive in nature. You have products that you can name, assign unique numbers, give reasonable OVERALL descriptions, assign category codes, etc. - but the detailed attributes of the items are a real nightmare of variability. On the other hand, many of the attributes are secondary to the nature of the item as a whole. And therein lies the key to understanding why you split out the attributes. You don't always need them.

When you are writing a detailed report (building a catalog?) you need to know the items in great detail. You need to describe them carefully and fully. But when providing inventory counts, when filing line items in a customer invoice, when providing general descriptions, you don't need the detailed attributes. Therefore, they are separable parts of your problems. (Not DISCARDABLE - just not always used.)

OK, next factor is that you need to have fields that make sense for everything in your table, but you have just stated that the variety is so great that you don't see it happening. So instead, turn the problem around into "How can I describe a varying, shifting line of products that doesn't even have the same number of attributes from one class to the next?"

By making a "meta" table to list attributes, and then include another meta table that identifies the specific attributes, you avoid the problem of fixed fields. Now you can have an item with 2 descriptive attributes and another one with 20 attributes. Better still, you only need to remember the attributes that apply. The rest can be sparse. If you are familiary with the techniques of object-oriented programming, you would realize that every object has its own set of attributes. Some are unique. Some are common. The common ones go into the main table. The unique ones go into the descriptive table. And the names of the attributes go into the attributes table.

Further, let's say you add a new product to the line and you find you need a new attribute you've never had to use before. So how do you do that? You add an attribute and attribute code to the attributes table. Then you add the item to the master table. Then you add the actual value of the attribute and its code to the descriptive table. (I said it that way assuming you were operating with relational integrity enforcement.)

On reports, your list of attributes might look like what I get for my CDs:

Title:  Thriller
------------------------
Artist 01           Michael Jackson
Artist 02           Vincent Price
Track 01...
Track 02...
etc.

The idea being I put the name of the attribute in col. 1, an index in col. 2 if one of the attributes is multi-valued, and the value of the particular attribute in column 3. Which turns out to be trivial as a report format.

Given this approach, a CD by a single artist and a CD anthology can be easily handled. It is just a matter of parent-child forms (with a JOIN between the attribute and attribute value tables).

Explore this idea. It is a good one. Bob didn't give you bad advice.

 

[btappan]

Bob, i see your design fundamentals for a normalized database, it just seems daunting for each new part number entry to indivually select the associated attributes and then value them every time. then on top of that without the part number being set as the key, it allows for duplicate entry of the same part number which could lead to further problems. The other problem I forsee is by the attribute fields not already being there to fill in for each part, there is the potential for one to be left out of a record all together.

 

[neileg]

without the part number being set as the key, it allows for duplicate entry of the same part number which could lead to further problems.

You can set the part number to be indexed, no duplicates without it having to be the PK

The other problem I forsee is by the attribute fields not already being there to fill in for each part, there is the potential for one to be left out of a record all together.

You have exactly the same problem no matter how you decide to hold the data.

You could have a table that holds the standard attributes required for each category of product. You could then use this table to append a set of blank attribute records to your attribute table when you are creating a new product record.

You'd still have the problem of making sure that a) an appropriate category already exists and b) that the user selects the right category.

 

[John_W]

Do you really need to keep the detailed specs as data? Or could they be countained in a narrative description? eg "this is a x volt widget of dimensions y and z". That narrative could then be stored in a text field, and you can have all your parts in one table.

 

[GaryPanic]

everyone is joining in on this one

as JW say
simplifie it as much as poss

so you couldd have
Product
Make
Dimensions(1)
Dimensions(2)

Description (Memo Field format)

This would reduce your admin down
it also allows you to have seaches by product rather than Unique Part numbers (Whcih you still Keep)

you could get what you are after , but you would need to be of the level of Bob or the Doc man or even Ghudson and this would be a great learning excercise for you - but I have found keep it simple where ever possible - cos sooner or later it's gonna go tits up - and if you complicated too much understanding what went wrong is going be blinking nightmare-ish

Keeping it simple - as long as it does the job is always the best route (My IT guru has always aid no system will do 1005 of what you want get to around 80% and thats good - he worked on my old system and I think he got close to 90 % (but we are talking over 4-5 years with various tweaks )

 

[btappan]

my problem is that it is often nessesscary for us to sort a table of parts in various manners when trying to search for a part or maybe a part that is similar that can be used as a substitute and it is much eaier to do when all the data is layed out in front of you and you can just click column headings.. I am trying to come up with one database that stores all of our product info as well as our stocking info for all items. Would it be easier to make one table with all the specs crammed into a "memo" field and then search those fields as long as i keep the fields to the same format and spelling and so on?

 

[John_W]

No, I wouldn't recommend that. If you need to sort/search by that data,then you really should take the approach suggested by Boblarson above. Though if that sounds too daunting I don't think you'd go far wrong with the suggestion by EdFred - it's not very elegant but would be much simpler.

 

[neileg]

No, I wouldn't recommend that. If you need to sort/search by that data,then you really should take the approach suggested by Boblarson above. Though if that sounds too daunting I don't think you'd go far wrong with the suggestion by EdFred - it's not very elegant but would be much simpler.

I second that.

 

[btappan]

well if i can keep it to say about 10 tables (eg,. (circuit breakers,boxes, batteries,terminals, etc.) to decribe similar parts to avoid the blank fields created if i were to keep it all in one which might creat 50 to 60 columns vs. about 10 ea if they were individual tables? is this a bad approach? if so why? thanks for all your input by the way.

 

[The_Doc_Man]

Separate tables is generally a VERY bad approach.

You multiply your maintenance by the number of tables.

The first time you have to build a comprehensive catalog, you have to write EXCEEDINGLY complex code to rejoin the tables or to otherwise revisit them. Heavens forfend that you might forget one of them.

Products ideally should be in one master table, regardless of how you structure the descriptive material. Otherwise you have something that might as well be done in Excel with flat files.

This is not a criticism, please don't take it that way. Only you know your level of expertise. If Bob's solution (or something like it) is something you cannot yet implement with your current level of knowledge, you are not ready to build this database in Access - given your previous problem description, that is.

As daunting a task as this may seem, the "different tables for different part types" solution is incredibly worse.

 

[RuralGuy]

Ben,
You are getting some *very* good advice in this thread.

 

[btappan]

So might it be best to take my 10 or so individual tables and import them into this format suggested by boblarson? haven't looked at doing it yet, would it be difficult? also BobLrson, you didnt clarify your field decriptions that i asked about in the 3rd or 4th post down.

 

[boblarson]

I think Neil answered those questions:

You can set the part number to be indexed, no duplicates without it having to be the PK

You have exactly the same problem no matter how you decide to hold the data.

You could have a table that holds the standard attributes required for each category of product. You could then use this table to append a set of blank attribute records to your attribute table when you are creating a new product record.

You'd still have the problem of making sure that a) an appropriate category already exists and b) that the user selects the right category.

 

[btappan]

so is this what i should end up with?

 

[boblarson]

Just about but where you have AttributeDescription in Table 2 and table 3, Table 3 should have Attribute Description (that which identifies what it is - width, height, etc.) and Table 2 should have AttributeValue (the value for the attribute number described by AttributeDescription).