Design question

[Guest]

Hi,
I am trying to nut out how to apply some db design logic but getting myself
confused. I thought to post to db design, but that's coming up as 'forum not
found' for some reason, so I thought I would try here for help as sooner or
later I need to end up with a form...

While not the exact situation, I have a table of 'tblStores', for which each
store has 'tblDepartments', and each department has multiple products,
selected from 'tblProducts'.

Because there are multiple products per department I have a linking table
'tblDepartmentProduct' which links the primary keys, allowing multiple
products for each department ID.

As far as I know my logic here is correct, but then translating this into
query / form design is confusing me. For a given store ID I am able to add
departments, but I'm lost on logic for adding multiple products for each
department - both at query level and then form layout.

Each department record needs to reference the store, and multiple products -
via the department product table though. This 4-way link is where I think the
confusion lies.

Any assistance to clear my thinking is appreciated.

swas

 

[Guest]

Do you even need the linking table (DepartmentProduct)? Looks like you
already have a one-to-many relationship between departments and products as
it is.

CW

 

[tina]

start by analyzing your entities. you have multiple stores. does each store
have all the same departments? does each department in each store have all
the same products? if yes to both, then are you tracking what happens to the
product inventory in each store - in other words, what is the purpose of the
database? you may only need a direct one-to-many link between departments
and products (assuming that each product is assigned to only one
department), and an indirect many-to-many relationship between products and
stores. on the other hand, if different stores do not have all the same
departments, and in those stores that do have some same departments, not all
the same products are carried, then your setup may need to be different.

you can't really give inexact, incomplete information and hope to get
on-target suggestions of a correct tables/relationships design for your
database.

hth

 

[Guest]

Thanks for all the responses. That's great.

I'll try and answer en mass.

All stores may have different departments, and each department different
products, or even the same products as other departments within the same
store.

The tblProducts is a master linked table used by all stores, so each store
can only reference the key field in this, not actually store records in
tblProducts. I have done it this way to ensure only one product gets listed
(ie. To avoid doubleups like 'WidgetHandles' and 'HandlesWidgets'). New
products need approval, then are available for everyone via the master.

My logic is therefore to have the tblDepartmentProduct as my many to many
link (Thanks Tina for reminding me that's what it is correctly called...) for
departments to products.

I acknowledge insufficient data makes it hard to offer advice, but as best I
can see it my initial info is still required in light of the above. It is
very difficult describing the layout in text, with no board to draw & sketch
designs on...

With a department selected, I want to add products, and it is here my
queries / forms logic is falling flat.

Again, thanks for the help, and I hope my info adds to the solution.

swas