question on table referencing a table.

[DMutzig]

Howdy all,

I am trying to build a db to track all the different projects I do on my web shop www.mutzigcreations.com. One thing I need to do is to be able to track the different materials I make things from. This wouldn't be to bad, but sometimes I use several different materials in one project and for the life of me I can't figure out how to do a table system (or form for that matter) that would let me do this.

I build out of woods, acrylics, and other special materials so I have a table for each of the 3 main types. Then I created tables called Material-1, Material-2, etc..that did table lookups to the 3 main type tables. These tables (material-1, etc..) are then related to my main table called Creations. (is this the correct way to go about it? as I can't seem to get it to work at all)

I would eventually like to set up a form that would have a check box next to Material-1, Material-2, etc...that would enable me to select:

A. if more than one material was used
B. What the materials were.

I didn't want to make several copies of the 3 main tables (wood, acyrilcs, special) as that seemed counter productive since the same info is used for each of the different material-1, material-2 selections.

Can anyone help me figure out what do to, or the best way to do it?

Thank you!
Doug

 

[neileg]

You have a many to many relationship. This is best modelled using a junction table. Do a search for many to many and junction table in these forums. It's been covered many times.

 

[DMutzig]

Neil,

Thanks for the info. I have been doing some reading up on junction tables and many-to-many. I have a long way to go but I wanted to test this idea...

am i correct to assume that with what I have the material-1, material-2, etc tables are the junction tables between the 3 main types and the creations table? or do I need to add something to the mix?

I am thinking I need a many-to-many between the 3 main types and the material tables, and then a 1-many between the material tables and the creations table...is this correct design?

Thanks for the info and guidence on this!
Doug

 

[DMutzig]

Totally Lost now

OK I have spent the past couple days going thru the forums looking into junction tables and many-to-many relationships, but I must be real dense on this. I can understand the concept but not how to apply it to my situation.

I have 3 tables (Woods, Acrylics, Specials) These tables have just 2 columns:
1: ID (like WoodsID, AcrylicsID, SpecialsID) ..which is autonumber and the primary key of the table.

2: Type of (wood, Acrylic, Special)..i.e. the name of the material.

I then have 4 tables (Materials-1, Materials-2, Materials-3, Materials-4) that I to have list all of the materials in the 3 primary tables (wods, acrylics, specials). They have columns of:

Material#ID (primary key..autonumber)
Woods
Acrylics
Specials (these three columns I used the lookup wizard on so that they reference their respective tables, but they are not primary keys).

My thinking on this was that I only want to have to input my materials 1 time, but I can make something from several different materials. So I wanted to be able to select more than 1 material for each project (I just started with 4 but once I get this figured out I might add more).

By having the Materials tables I could select several different materials for one project, but have the information come from only the 3 base tables.

The Materials Tables are linked to a table called Creations, where I'll be inputing all the different things made.

So I was thinking that the Materials tables had a 1-many relationship with the Creations Table, as well as having a 1-many relationship with each of the 3 primary tables (woods, etc..). Am I correct in this planning so far?

What I can't seem to figure out is: where would a junction table go? Are the Material tables the junction table? And how would I make a junction table (sorry I don't truly understand what links to what).

I have everything else setup for the database, with a data entry form that lets me add in materials to their different tables (woods, acrylics, specials) as well as all the other data. I then have a form for Creations that so far lets me set all the different variables for a specific creation except for the materials it is made of.

Including a pic of the current relationships I have (note I have been playing with this and Materials-1 table isn't connected right now)

Any help/guidence on this would be great and I thank you all very much for your time and expertise!

Doug

 

[john471]

Your design does not conform to the rules of database normalisation. Do a search on "normalisation" and read up on it - understanding and adhering to these "rules" will save you hours of pain later that a poor design would otherwise inflict.

 

[borgia]

Without going into your situation in detail, the following occurs to me: -
You should have a table which called tblCreationMaterials which has it's own id, Creation id, Material id. The Creation table is linked to this in a One to Many relationship via the Creation id fields. The form for Creation can have a subform for the materials where you can list as many as you wish.
Also,
Why don't you have one table for materials. It can have an autonumber field and list all the different types of material. You can differentiate the materials with a field of material type. If they have different attributes, you can include fields in the table for all of them and only fill in the relevant fields for the particular material type.
By having all materials on one table you can then link them to the tblCreationMaterials table and choose them using a lookup field in your Creation subform.

 

[DMutzig]

Thanks!

Thank you all for the input on this.

I'll try out the idea of putting everything in one table (was thinking on that one earlier) and just adding in some extra fields to it. On question though...using the tblCreationsMaterials would I be able to select more than 1 material from the list? to do I need to do something like (tblCreationsMaterials1, tbleCreationsMaterials2, etc..) and does that break normalization?

learning can be fun..that my matra on this

Thanks again!!!

Doug