Relationships - Composite Key?

[reneelacks]

I'm creating a database that shows what I thought were simple relationships, ex. Manufacturer w/many Products; Products w/Many Chemicals and a drop down box in the PRoducts table to select multiple sites where the products are located. This has worked well until I was told I needed to add Site specific locations where the products could be found. In other words, the location needs to be specific to the Product AND the site. Is there any way to accomplish this without completing starting over. My users like being able to select their specific site from a drop down list.

 

[jdraw]

Can you give us the 30,000 ft overview of thew purpose of the proposed database in 5-6 lines of plain English?
Is it the Site of the Manufacturer, or is it some other "storage" location that is involved? Do you display anything about the Chemicals in a Product? If so, is that by Location also?

Recommend you
-get your tables and relationships established before getting too deep into HOW to do things,
- do not use dropdowns at the Table level

 

[reneelacks]

The sites are warehouses where the products are stored. Products are Manufacturer specific, and each product contains its chemical list. Locations now need to be added, (i.e. MotorOil DW30 is stored in 3 different Warehouses (Sites), but the specific location is different in each warehouse:
Warehouse #1 - Location - 2nd Fl;
Warehouse #2 - Location - 3rd Fl, Rack 2
etc.


(Unfortunately I thought I was done when I was hit with the addition of the "Work Area" & "DateAdded") - I realize I may be forced to start over
I've attached a copy of the database

See SDS - Safety Data Form - "Work Area" - would represent Locations

 

[Fran Lombard]

Sounds like your simple database is morphing into a full blown inventory system.
Make sure you have a complete list of the requirements and start modeling.

Take a look at some of the data models available on the web to get an idea of what has worked for others.

Based on the little bit of info from your last post I think you need at least the folloing additional tables:

Warehouse or Site - Physical Building
Bins - Specific locations in the warehouse
Lots - Quantity of a particular Product in a bin

Additionally you now may need to track ins and outs to/from these lots.

Just food for thought - hope it helps
Fran

 

[jdraw]

I agree with Fran's observations. When dealing with any database application, it is advisable to get a clear statement of the business/rules involved. Make sure the manager/owner/requestor of the system agrees with the business definition then create a data model that addresses/adheres to the business rules. If you get it designed at this level, you can build pieces knowing they will fit together.

Good luck with your project.

 

[gemma-the-husky]

if you are drifting into a bigger project, bear in mind that inventory is one of the hardest things to do.

Depending on what your main role in the business is, it is certainly worth clarifying the ultimate goal of this system. Otherwise what started out as a modest departmental idea, could easily become an unwieldy problem.