I'm working on a school project that requires the creation of an ER diagram and a database schema that is derived from that diagram. The schema has some very stringent size requirements (no more than x number of tables). I'm running into an issue where some of my entities follow an "is-a" type relationship that is best described using the EER model, something we aren't allowed to use. Here's a very rough outline of what I'm trying to achieve:
I need to keep track of several unique [Part]s that have many (entirely unique attributes). They all share some attributes, like (QuantityInStock). I have [Machine]s that use any (quantity) of any of those different [Part]s.
If I follow the ER model, I believe the machines will have a separate M-to-N relationship with each unique part type. Each M-to-N relationship is supposed to translate into its own table. Because of the strict maximum number of tables we are allowed to have, this isn't possible.
If I follow the EER model, the translation process generates only one or two extra tables - not one more relation for each [Part].
What I need to accomplish is somehow convert the "is-a" relationship (i.e. [ThisPartType] "is-a" [Part]) into something that works within the ER model. I need some way to relate of the different unique parts to the machines that use those parts.
To give an example of what I'm going for:
Machine A uses 30 of PartA[1], 47 of PartB[21], 22 of PartC[18], and 3 of PartD[54].
Machine B uses 8 of PartC[12] and 1 of PartD[44].
Machine C uses 1 of PartF[0] and 5 of PartZ[28].
I'd like to have access to the unique bits of information for PartB[21] (radius, threadwidth), as well query the heaviest of all the PartCs. But I also need to have the ability to assign these parts to the Machines in a way that doesn't need many more tables.
I hope that makes some sense. Thanks for any hints you provide.
