DB design

[Guest]

I have to design a DB which contains a parts table with components as records
and tables which contain information about these components. Now, the
different component types may have a different number of attributes.
What is the better approach?
Keep all parts in one table with the maximum number of fields determined by
the part with the maximum attributes (this would leave many fields empty) or
keep the parts in separate tables. How would I set the relation between the
parts table and the different component tables?

Any hint or help is highly appreciated.

 

[tina]

if i understand correctly, you're using the terms "parts" and "components"
interchangeably - you're actually referring to one *entity*. (i'll refer to
this entity specifically as "parts", to avoid confusion.) and each part has
a number of attributes that describe it; not always the same number of
attributes. suggest three tables, as

tblParts
PartID (primary key)
PartName

PartID PartName
1 A
2 B
3 C

tblAttributes
AttributeID (pk)
AttributeName

AttributeID AttributeName
1 Length
2 Weight
3 Composition

tblPartAttributes
PartID (foreign key from tblParts)
AttributeID (fk from tblAttributes)
AttributeValue
<you can use both fields as a combination primary key, since you don't want
to list a specific attribute for a specific part more than once. or, if you
prefer, you can add a third field to the table (probably Autonumber) and set
it as the primary key for the table.>

relationships are
tblParts.PartID 1:n tblPartAttributes.PartID
tblAttributes.AttributeID 1:n tblPartAttributes.AttributeID

each part is listed once in the parts table. each possible attribute is
listed in the attributes table. the specific attributes for each part are
listed in the parts/attributes table, along with the "value" of each
attribute. so if part B has 2 attributes, then there will be two records in
tblPartsAttributes, as

PartID AttributeID AttributeValue
2 1 6 inches
2 3 plastic

hth

 

[Guest]

Hi George,

In my opinion what you could do is have a table in which you would have
attributes have attribute name and an attributeId. One table for the parts,
part name. One table has part id, componentid,attributeid, yes/no to indicate
if the attribute applies or not. The parts table can have a componentId, so
instead of having to store the component name in the table, you can store the
componentid. You can connect the parts and attribute table using the
attributeid, parts and compontens tbale by using the componentid.

Hope you find this uself, let me know, and hope its not confusing.

Anand

 

[Guest]

Tina, Anand,

many thanks. Both proposals gave me a good insight and really helped me to
solve my problem.

 

[tina]

you're welcome

Tina, Anand,

many thanks. Both proposals gave me a good insight and really helped me to
solve my problem.

 

[Guest]

you welcome
--
"Who will guard the guards?"

Tina, Anand,

many thanks. Both proposals gave me a good insight and really helped me to
solve my problem.