Little M:M problem...

[opopanax666]

Hi guys & gals,

Let's say I've got a table with factories, and a table with products. Several of the factories make several of the products, so M:M.

BUT, some of the products of one factory are resources to an other factory, and since I want to show (in a form) what each factory needs and what it produces, and because the product and resource list is the same, I'm in a bit of a pickle...

Or am I missing something here?

Any help more than welcome!

 

[llkhoutx]

You're missing a juntion table which indicates which products are utilized by which factory. A product manufactured by one factory and utilized by another factory will have two entries.

That junction table will have values (they should be "keys" to the factory table and product table, respectively) as follows:

F11
F21
F22

in your notiation.

 

[opopanax666]

In the entire database, I've got several M:M relationships, so working with junction tables isn't the problem. The problem lies in the fact that I've got a table with all the resources/products, and one table where some of these should figure as Resource and some as Products...

e.g. Wheatfarm has Wheat as Product, but Pigfarm has Wheat as Resource. How do I implement the difference between Products and Resources, when they both come from a single table? I could do it, if I copied the Resource table, and used the copy for the Products, but it seems to me I should be able to use one table for both...

 

[llkhoutx]

Create a foregn key field which links to another table, ProductType.

You've stumbled onto table normalization. You're on the right track.

Normalization of tables is the most important thing that one can to in table design. Failure to do it causes lots of headaches down the road.

 

[opopanax666]

Sorry, but I'm still a noob, so some of the used terms are lost on me Here's what I got this far:



It's from a game, but I thought it represented some of the possibilities of a database.

Or is it this one?