Setting up a many to one DB

[Splinters]

I am setting up a DB for my wood turning shop, and have a table for the wood, and am setting up one for the completed bowls.

Table #1, tblWoodInventory, has the wood info - species, size, source, cost, etc. - and has the following superkey:
{species, last 2 digits of the year purchased, sequential count for the species that year}.
The table has a field that contains the link to the key in table 2.

Table #2, tblProductInventory, has the bowl info - the key (see below) ID's the bowl, plus there is price, gallery where placed, etc.
The superkey is similar to #1:
{species,enhancements, last 2 digits of the year purchased, sequential count for the species that year}. Enhancements can be special features of the wood - figuring, burl, spalted - and/or added items - dye, metal leaf treatment, scorching. It can also include additional woods used - in a laminated or segmented bowl.

The problem I have is the additional woods - I can link from each wood to the same bowl, but how do I set up the second table to know which is the primary wood, and which are the additional components? I can limit the number of woods noted in the key, but still need to include the other woods used to properly track the materials used for inventory purposes.

And, perhaps the key question I have, how do I do this and keep the key intact?

I'm new to Access - do have programming experience, but not in DB work.

Thanks for any suggestions,

Stephen

 

[The_Doc_Man]

OK, with no DB background, the first thing you need to look at is a list of articles on "Database Normalization" - which relates to key design and key content among other things. Access has Help on that subject. Wikipedia (.org) has a nice article. You can also do a really nice Google search - but for the latter, stick to .EDU sites for colleges and universities you have heard of.

The moment you say "Superkey" you are looking at "Overkill" in Access.

Given the complexity of the work you describe, I seriously doubt that two tables is enough. However, it is not clear to me how many WOULD be enough. The issue is that I don't grasp the things you want to store.

Search this forum (it has a nice SEARCH function) on how to determine entities. Since you have programming background, I'll add that entities in Access are things you put in tables to form a structured list. Each element of the list must of course have the same structure. That uniformity means that as a programmer you will be on familiar grounds. Some of the other concepts for DBs won't be as familiar.

You might wish to also search for the subject of JUNCTION tables, which is a way that one can describe a list of distinct objects that have multiple but varying components. In the "Design and Theory" section, Banana and I have engaged in long dialogs about how to design multi-attribute tables representing properties of things he was tracking. Very different things - but very useful concept.

 

[Splinters]

OK, with no DB background, the first thing you need to look at is a list of articles on "Database Normalization" - which relates to key design and key content among other things. Access has Help on that subject. Wikipedia (.org) has a nice article. You can also do a really nice Google search - but for the latter, stick to .EDU sites for colleges and universities you have heard of.

Yeah, I've been doing that - but some of the syntax is so cryptic that it's hard to follow on some of them. I think my biggest problem is just working out the functionality of the Access framework that I don't quite understand yet. And, as typical for me I am trying to do one specific task without having to learn the entire system - and having to work with Access 2000 and most of the things I see seem to relate to later versions, so it's hard to follow sometimes.

The moment you say "Superkey" you are looking at "Overkill" in Access.

Well, I was really only trying to impress myself with that - read it in the O'reilly book "Access Database"...

But, if I shouldn't use the 3 fields as the key, should I construct a single key field by parsing and concatenating the 3 into one? I think I can manage that, just wasn't sure if it was the best way. Come to think of it, that might be the best answer to my question regarding the multiple component of the second field key - if I don't have the added woods or enhancements, they could just be a "null" value or excluded by an if..then...else expression.

Given the complexity of the work you describe, I seriously doubt that two tables is enough. However, it is not clear to me how many WOULD be enough. The issue is that I don't grasp the things you want to store.

Basically I have a wood blank with a specific ID code in my inventory, when I use it to make a bowl I give it a new ID code to indicate when the bowl was turned plus any additions beyond just the wood itself. The bowl is then inventoried under that new ID, but I need to track back to the blank to see what the costs were & what profit I made after the sale. If it were always one blank to one bowl, there would be no problem, but adding the enhancements generates all kinds of permutations...

I currently do this manually in Excel, but want to automate as much as I can to eliminate the potential of duplicate ID's - which is easy to do when starting with almost 400 pieces of wood!

Search this forum (it has a nice SEARCH function) on how to determine entities. Since you have programming background, I'll add that entities in Access are things you put in tables to form a structured list. Each element of the list must of course have the same structure. That uniformity means that as a programmer you will be on familiar grounds. Some of the other concepts for DBs won't be as familiar.

Yeah, the concepts of classes and attributes are pretty clear to me, and I seem to grasp the relationship aspects in general, but the fine points of DB design are not something I had a need to work in as a C++ programmer in an engineering office. Plus, being tight on time I didn't really want to embed myself in a full scale learning program. It's beginning to seem that I may need to find that time, just get a book and spend a week or two tearing the rest of my hair out

You might wish to also search for the subject of JUNCTION tables, which is a way that one can describe a list of distinct objects that have multiple but varying components. In the "Design and Theory" section, Banana and I have engaged in long dialogs about how to design multi-attribute tables representing properties of things he was tracking. Very different things - but very useful concept.

I'll take a ramble through that section and see just how deep this pond is...

Thanks for the suggestions.

Stephen

 

[John_W]

I think what you basically have is blocks of wood, and products made from that wood?

Each product may be made from one or many blocks of wood, and each block may be the source of one or many products. So you have a many-to-many relationship between wood and products, which you will need a junction table to resolve.

I'm not sure what you're getting at with keys, I'd just forget about that.

 

[The_Doc_Man]

Splinters, I would point out something that John W's comments reminded me of.

FIRST you identify the entities. SECOND you design keys for them. THIRD you identify how they relate. Then, taking relationships into account... FOURTH, see if your new knowledge identified more entities. FIFTH, design keys for them. SIXTH, see how the new data relates to what you had. Shall I go on to SEVENTH, EIGHTH, NINTH... ? You get the idea. It is like an old washer with a wringer. You run everything through the wash and wring it out to see what comes out. Since you have a C/C++ background, you will understand the iterative design concept. What you need to worry about more is that Access and VBA are strongly typed as opposed to the C family, which is much more weakly typed for the most part.

Questions (of a rhetorical nature) for your consideration as design elements.

1. You've got wood blanks. Are they identifiable such that you can tell the difference between two pieces of (say) 8" x 8" x 8" ash with no bark or major blemishes? I.e. is there an inventory number you have assigned to tell them apart? If so, you have your identifier. IF NOT, you have an issue to consider regarding complexity of identification of your inventory. Another issue is defining the attributes of the blank. I can think of several issues you might wish to track. In no particular order,

Material: Ash, Beech, Cherry, Cypress, Douglas Fir, ..., Oak, Pine, ... Sycamore, etc...
Height, Width, Length (particular units)
Grain: fine, medium, coarse
Knots - none, some, many ... AND Knot size: small, medium, large

The question for your blanks table is whether each of these attributes can be described with a simple code, a brief word, or how detailed you take it. If you stopped where I did, you need only one table for your blanks. The catch is identifying which blank is which if there is ever a case where two blanks of the same size and material will have different enough "other" characteristics that you need separate records to distinguish them. This governs your table of blanks.

2. You've got bowl designs. I assume you have model numbers of some sort that you use when you make them. This is a model X, that is a model Y, this is a model Z. This gives you a ready-made prime key for the bowl design tables. The catch is that it might have a list of attributes for the blank or blanks you use. So you would have a table of designs and might have a CHILD table of raw materials required to make this particular design.

3. When you describe a bowl you have made, though, it has attributes - like which of many possible designs you chose and which blanks went into it. This is a JUNCTION table between bowl design and blanks used, and THIS is the table that describes a finished product.

4. You probably also need to consider supplies such as varnishes, stains, and paints as things you can use on your bowls. And how you would indicate their use for a given product (see #3). If "paint" is vulgar in regard to what you make, don't take it personally. I don't know from your earlier discussion whether you paint the bowls with designs when you are done.

This is how I might start. But it might not end there.

 

[Splinters]

I think what you basically have is blocks of wood, and products made from that wood?

Each product may be made from one or many blocks of wood, and each block may be the source of one or many products. So you have a many-to-many relationship between wood and products, which you will need a junction table to resolve.

I'm not sure what you're getting at with keys, I'd just forget about that.

Yeah, basically that's it. And usually it's a one to one relationship - a block is used to make one bowl, and only one bowl.

However, I think it's actually a many to one because in some cases more than one piece of wood goes into making one bowl. This might be a bowl that has 2 or more pieces of wood glued together into a pattern of stripes or some decorative manner. These are rare - so far - but who knows what the future may bring?

In the wood table, I currently have a field with the code for a block of wood - made up of the following text parts:

Part 1: Species of wood - Ash, Elm, Maple, etc.
Part 2: The 2 digit number of the year the wood was purchased - 2007 is shown as 07.
Part 3: A ":".
Part 4: The 3 digit number of the purchase sequence of that species, for that year.

So, the sixth Ash blank purchased in 2007 had the following first field: "Ash07:006".

Other fields follow with size & cost, etc.

Included is one that has the same info combined into a code similar - but different - as the wood blank that is the foreign key (if I have that right) for the bowl table (note the additional part):

Part 1: Species of wood - Ash, Elm, Maple, etc. More than one wood may be used.
Part 2: A code for and embellishments - things beyond the wood itself done to make the more distinct.
Part 3: The 2 digit number of the year the bowl was turned - 2007 is shown as 07.
Part 4: A "-".
Part 5: The 3 digit number of the turned sequence of that species, for that year.

So, the twenty third Maple bowl turned in 2007 - with a copper leaf trim added - had the following first field: "MapleCopper07-023".

And the first Ash Bowl with a Wenge laminate on top in 2006 is coded as "AshWenge06-001"

And that shows the problem I asked about - since I could use several woods in one bowl (plus any number of embellishments!), is there an efficient way to set up the bowl table to allow more than one wood (could be ten or more in a segmented bowl) without wasting space for the vast majority that only have one type of wood?

Currently, I construct both keys manually - but would like to simply enter the parts in separate fields and have Access construct the key automatically. I think I can figure out how to do this last (at least for the wood table), but am unsure how to set up the bowl table to provide for additional wood plus embellishments, and do it efficiently without having a separate field for each possible atomic element.

I realize I may be making more trouble than needed in this - just trying to see how far I can use Access to do the work for me...and avoid possible errors by duplication or skipping sequence numbers.

Hope this makes at least some level of sense.

Thanks,

Stephen

 

[Splinters]

Splinters, I would point out something that John W's comments reminded me of.

FIRST you identify the entities. SECOND you design keys for them. THIRD you identify how they relate. Then, taking relationships into account... FOURTH, see if your new knowledge identified more entities. FIFTH, design keys for them. SIXTH, see how the new data relates to what you had. Shall I go on to SEVENTH, EIGHTH, NINTH... ? You get the idea. It is like an old washer with a wringer. You run everything through the wash and wring it out to see what comes out. Since you have a C/C++ background, you will understand the iterative design concept.

Yeah, no problem there. I am wondering if you are saying that I need a separate table(s) for the possible enhancements? Might make some sense, I'll look at it & see if that would work. I can see how it would allow a large number of variations, while leaving the wood & bowl tables fairly simple.

What you need to worry about more is that Access and VBA are strongly typed as opposed to the C family, which is much more weakly typed for the most part.

So I've noticed...

Questions (of a rhetorical nature) for your consideration as design elements.

1. You've got wood blanks. Are they identifiable such that you can tell the difference between two pieces of (say) 8" x 8" x 8" ash with no bark or major blemishes? I.e. is there an inventory number you have assigned to tell them apart? If so, you have your identifier. IF NOT, you have an issue to consider regarding complexity of identification of your inventory. Another issue is defining the attributes of the blank. I can think of several issues you might wish to track. In no particular order,

Material: Ash, Beech, Cherry, Cypress, Douglas Fir, ..., Oak, Pine, ... Sycamore, etc...
Height, Width, Length (particular units)
Grain: fine, medium, coarse
Knots - none, some, many ... AND Knot size: small, medium, large

The question for your blanks table is whether each of these attributes can be described with a simple code, a brief word, or how detailed you take it. If you stopped where I did, you need only one table for your blanks. The catch is identifying which blank is which if there is ever a case where two blanks of the same size and material will have different enough "other" characteristics that you need separate records to distinguish them. This governs your table of blanks.

OK, refer back to the response I just sent to John W. for the details.

As to the wood, I use just the species for the wood ID. What I forgot to mention to John W. is that some specific characteristics of the wood are noted in the code for the bowl - primarily if the wood is burl, figured or spalted. These characteristics impact the appearance of the bowl, and the price I can ask for it. Since they do not impact the wood itself, they are not noted in the wood code in table one.

2. You've got bowl designs. I assume you have model numbers of some sort that you use when you make them. This is a model X, that is a model Y, this is a model Z. This gives you a ready-made prime key for the bowl design tables. The catch is that it might have a list of attributes for the blank or blanks you use. So you would have a table of designs and might have a CHILD table of raw materials required to make this particular design.

OK, one thing I do not do is use a template or design for the bowl - all of my bowls are free form, using the feeling I get from each piece of wood to shape the finished bowl. Each bowl is a unique item - it may be similar to others, but no two are ever the same by intent. If I ever get to the point where I start mass-producing them, I will quit turning. That may sound a bit egotistical - but being retired and with a stable income, I can afford to view my work as an "art" rather than simply a "product". Frankly, if it weren't for the IRS regs that require this tracking, I'd probably just make them and not worry about any more than covering my costs...

In a perfect world, of course...

3. When you describe a bowl you have made, though, it has attributes - like which of many possible designs you chose and which blanks went into it. This is a JUNCTION table between bowl design and blanks used, and THIS is the table that describes a finished product.

And here is where my lack of knowledge of DB design kicks in - I'm not sure how a Junction works...I think it's the result of the needed parts of several tables being compressed into one table to meet the need you have at the time. What I don't see is how that works in my case. Since the bowls are the final result, isn't the bowl table the top level table? Yes, as you point out below, other components (oils, wax, polish & metal leaf) could come into it from a 3rd table - plus one for the venue that is selling my work - but I see the bowls as the final result, not just a component. Or is this too simplistic on my part?

4. You probably also need to consider supplies such as varnishes, stains, and paints as things you can use on your bowls. And how you would indicate their use for a given product (see #3). If "paint" is vulgar in regard to what you make, don't take it personally. I don't know from your earlier discussion whether you paint the bowls with designs when you are done.

Not at the present time - but I do dye some, and am looking at adding pyrographic designs (in plain English, wood burning), as well as using inlays to fill voids & cracks for design purposes. I would probably include most of these in the ID code as an embellishment.

But, I see your point - more layers of layered complications...

This is how I might start. But it might not end there.

Oh, how well I understand that...

Thanks, Doc.

 

[Splinters]

So you have a many-to-many relationship between wood and products, which you will need a junction table to resolve.

OK, John W. - It appears that my response to your post was incorrect in that you were right about the relationship being a many-to-many and not a many-to-one as I assumed (not realizing at the time that such a relationship does not exist...).

Anyway, it seems to me that I may be misunderstanding a lot of this. Sorry about my confusion - I've been told that I inherited it from my kids...

I'm going to spend the weekend with a good book on Access & see if I can get a better grip on this.

Thanks for the help.

Stephen

 

[The_Doc_Man]

The concept of many-to-many is where that junction table comes into play.

But since you don't do explicit designs, you might have limited one side of what I was considering. You certainly have more than one bowl as a finished product, and therefore that is a "many". You have more than one possible material used in its making, and that is a many. But there IS no "design" table. Still, there might be a description table, showing after the fact what you did, and there is your "many."

I'm thinking like this:

tblBowl
BowlID, prime key, could be autonumber if you want.
CreatedOn, date
AskingPrice, currency
SoldFor, currency

tblWoods
BlankID, your ID for a source of wood
Species
Size elements
other data

tblBowlWoods
BowlID, Foreign Key to Bowl table
WoodID, Foreign Key to Wood table
(an entry in this JUNCTION table means that wood WoodID contributed to the making of bowl BowlID.) This is the center of the many-many relationship between wood blanks and finished bowls. You have ONE entry here for each blank that contributed to a bowl. you have at least one entry here for each bowl but could have multiple entries for bowls with laminates.

You would have a raw-materials table for varnishes, dyes and stains, paints, glues, and other consumables. There would be a similar junction table between a bowl and the table of your consumables.

Other ideas come to mind but I don't want to barrage you. Further, I'm not deeply enough into making things with my hands to fully appreciate what you do. (By training, I'm an analyst, not a synthesist. I take things apart to see what makes 'em tick.)

 

[Splinters]

The concept of many-to-many is where that junction table comes into play.

But since you don't do explicit designs, you might have limited one side of what I was considering. You certainly have more than one bowl as a finished product, and therefore that is a "many". You have more than one possible material used in its making, and that is a many. But there IS no "design" table. Still, there might be a description table, showing after the fact what you did, and there is your "many."

I'm thinking like this:

tblBowl
BowlID, prime key, could be autonumber if you want.
CreatedOn, date
AskingPrice, currency
SoldFor, currency

tblWoods
BlankID, your ID for a source of wood
Species
Size elements
other data

tblBowlWoods
BowlID, Foreign Key to Bowl table
WoodID, Foreign Key to Wood table
(an entry in this JUNCTION table means that wood WoodID contributed to the making of bowl BowlID.) This is the center of the many-many relationship between wood blanks and finished bowls. You have ONE entry here for each blank that contributed to a bowl. you have at least one entry here for each bowl but could have multiple entries for bowls with laminates.

You would have a raw-materials table for varnishes, dyes and stains, paints, glues, and other consumables. There would be a similar junction table between a bowl and the table of your consumables.

Other ideas come to mind but I don't want to barrage you. Further, I'm not deeply enough into making things with my hands to fully appreciate what you do. (By training, I'm an analyst, not a synthesist. I take things apart to see what makes 'em tick.)

OK...that makes sense, and does seem to solve the problem of multiple materials.

I'll play around with it this week, probably will have some more questions later.

Doc, many thanks to you and John W. for the great - and very patient - assistance.

Stephen

 

[Splinters]

OK, I had a chance to work on this - and, while most of it is still as clear as sheepdip , I did get something close to what we were talking about...close, but not quite there yet.

I've attached a zip file of the sample DB - with just the basic elements and a few records.

tblWoodInventory has txtWoodIDcode (Primary key) & txtProductCode (which is the field I've been using to track the relationships manually)

tblBowlInventory has txtBowlCode (Primary key)

tblWoodBowl has txtWoodIDcode & txtBowlCode

qryWood_Bowl_relationship shows all 5 fields.

There are 2 forms showing just the contents of each inventory table, and 2 which show the relationships between the table fields - and seem to show it is working properly.

The format of the forms is still a bit from what I want but that will come later.

For now, I'll leave the txtProductCode in the wood inventory table just to be sure it all works as it should...

Any suggestions would be appreciated.

Thanks, Stephen

 

[John_W]

I don't like to use a field with meaningful data as a primary key. I'd simply use an autonumber field as PK, with data about wood type, when purchased, cost etc as additional fields.

 

[Splinters]

I don't like to use a field with meaningful data as a primary key. I'd simply use an autonumber field as PK, with data about wood type, when purchased, cost etc as additional fields.

John, I can see the logic in that - and may change to that once I know everything is working the way it should. For now, I need both codes to be unique and sequential. Which, I understand, autonumbers would do - but I've been working these by hand for some time, so it's just an old habit at this point.

Thanks for the comment,

Stephen

 

[Rabbie]

John, I can see the logic in that - and may change to that once I know everything is working the way it should. For now, I need both codes to be unique and sequential. Which, I understand, autonumbers would do - but I've been working these by hand for some time, so it's just an old habit at this point.

Thanks for the comment,

Stephen

Just remember that you cannot guarantee in the real world that you won't get a gap in the autonumber sequence. They are guaranteed to be unique but it is possible to have a break in the sequence.

 

[Splinters]

Just remember that you cannot guarantee in the real world that you won't get a gap in the autonumber sequence. They are guaranteed to be unique but it is possible to have a break in the sequence.

Which is one reason I didn't use them in the first place.

From what I have seen, the only real requirement for Access is that the key be unique - and not null - exactly what my code needs as well. And, this way, I can see any problems such as gaps immediately, just by scanning the table visually.

Stephen