Many to Many

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ernst Guckel - 17 Jul 2005 04:10 GMT

Hey,

I have an inventory database with the table design as follows:

tblItems
ItemID(AN)
ItemNumber
ItemName
...

tblInventory
InvID(AN)
ItemID(tblItems)
InvDate
InvRecieved
InvTransIN
...

The database works from what I can see but after thinking about it the
database really has a many to many relationship. Items have Many inventories
and inventories have many items... I should have used a junction table. My
question is where is the downside to my current design scheme? From what I
can see there is no redundancy...

Ernst.

Reply to this Message

tina - 17 Jul 2005 05:15 GMT

if you "used a junction table", then your tables would be

tblItems (a list of all items)
tblInventories (a list of all inventories)
tblInventoryItems (the linking table where you list each item counted in
each inventory - your tblInventory already fits that description, i just
rename it here for clarity of purpose)

so, in essense you already have the "linking" table - what you're missing in
a many-to-many equation is the "other" parent table: tblInventories.

so the question is: is an Inventory an "entity"? does it have specific
characteristics that you want to record - data that describes the inventory
entity itself, NOT the items being inventoried - besides the date that it
takes place? (there's no advantage to moving the Date field from your
current tblInventory into a tblInventories, unless there are other
characteristics in tblInventory also.)

for instance, is a given inventory specific to a particular department? is a
given inventory the responsibility of a particular manager? is a given
inventory focused only on a particular category of items? etc, etc, etc.

if an Inventory is indeed a definable entity, then you need a tblInventories
(for those fields that describe the entity) and a tblInventoryItems. your
tblInventory is suitable as the linking table tblInventoryItems (see the
table list at start of my post) - all you would have to do is add the
primary key from tblInventories to it.

but, if an Inventory is NOT a definable entity, then the table design you
posted looks correct to me.

hth

> Hey,
>
[quoted text clipped - 21 lines]
>
> Ernst.

Reply to this Message

Ernst Guckel - 17 Jul 2005 06:07 GMT

Thanks for the info. An Inventory is not really an entity to itself but a
listing of specifics to Inventory Items... So I understand what you are
saying and agree that it is correct the way it is... The next question would
be can I incorporate the same design I have now into a payroll
enviorment...IE:

tblEmployees
EmpID(AN)
EmpName
...

tblPayroll
PayrollID(AN)
EmpID
PayDate
RegHours
OTHours
VacationHours
...

Or am I missing something here... Besides the Date I don't see how Payroll
is an entity to itself... even though it can be argued that it is the left
many in a many-to-many relationship... I cannot tell when or when not to use
a joining table to establish a many-to-many relationship

Ernst

> if you "used a junction table", then your tables would be
>
[quoted text clipped - 57 lines]
> >
> > Ernst.

Reply to this Message

tina - 17 Jul 2005 11:01 GMT

> Besides the Date I don't see how Payroll
> is an entity to itself... even though it can be argued that it is the left
> many in a many-to-many relationship...

a many-to-many relationship is always between two entities: one Order may
consist of many Products AND one Product may be included in many Orders.

> I cannot tell when or when not to use
> a joining table to establish a many-to-many relationship

in a relational data model, you don't use a joining table to *establish* a
many-to-many relationship, you use it to *resolve* one. and YOU don't
"decide" that tables have a many-to-many relationship, the table
relationships are determined by the real-world relationships you're
modeling.

the first step in creating a database is to walk away from your computer.
sit down with pencil and paper, analyze the business process you plan to
support with a relational database (in all but the simplest databases, this
is often a multi-step, multi-day process), define the "real-world" entities
(tables) you need to track and the data elements that describe each entity
(fields), and normalize the entities/data. define the relationships between
those entities: one-to-one (rare), one-to-many (predominant), and
many-to-many (not unusual, but can't be modeled directly in a relational
design, so it must be "resolved" with a linking table - used as the "many"
side in a one-to-many relationship with each entity table). in each
relationship, determine which is the dominant (parent) table and which is
the subordinate (child) table. in a one-to-many relationship, the "one" side
is the parent table. in a "resolved" many-to-many relationship, both entity
tables are parent tables, and the linking table is a child table to each. in
a one-to-one relationship, it's not clear-cut; you have to determine which
is the parent table based on the reason for using this rare relationship -
in practice, it's usually pretty clear which table is the parent and which
is the child.

when you've done all the work described above, *and not before*, then you're
ready to sit at the computer and begin building the table/relationship
structures in Access (or any other relational database tool). the above
outline may sound detailed, but i've given it to you "in a nutshell".
relational data modeling is the most difficult, and most crucial, aspect of
building any relational database application; most people struggle with the
concepts, and master them through reading, study, and practice. one good
text on relational data modeling is Database Design for Mere Mortals by
Michael Hernandez. the following website also has many links to valuable
information about tables/relationships and normalization:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.
this website also includes many, many other resources to help you as you
move through the database development process.

hth

> Thanks for the info. An Inventory is not really an entity to itself but a
> listing of specifics to Inventory Items... So I understand what you are
[quoted text clipped - 84 lines]
> > >
> > > Ernst.

Reply to this Message