Relational Database Structure Storing Unique Attributes of SubCatagories

I am creating a database of structures that can be of different types. Each of the types has a set of unique attributes such as the materials, shape, properties related specifically to the type of structure.

For instance, a structure is a bridge which can have a span length and material type for the deck.

Another instance of a structure would be a wall which has a height and material type and thickness.

I do not want to generate a flat table that contains all possible attributes as there are many types of structures that could exist and the attributes are flexible and there are numerous attributes to store.

Basically we want to prevent records from getting too large by storing unique attribute data in a separate table for each type of structure but I can't think of a way to link the generic structure table to point to the attribute tables easily.

==================== EDIT,

Now that searched for "generalization specialization relational modeling" it appears that I want to model the data as a object oriented structure with a parent class "structure" that has child classes that inherit the common information as bridge structure, wall structure. The only issue now is that I am trying to help a engineer make a simple stand alone Access 2010 database but it ap开发者_运维百科pears that I am correctly generating the relationships within access with the primary key of the parent class being the primary and foreign key of the child classes. This appears to cause an issue inside Access 2010 where it wants instances of the parent to contain one of each child class which is non-sense.

Basically I have,

StructureID as the primary key on both structure, bridge, and wall tables on the bridge and wall table their primary key which is the structureID links back to the structure table. I am not too familiar with Access 2010 and don't know why it doesnt work.

From one of the IBM papers,

Basically want to make this in an Access 2010.

I suggest trying to split the structures into separate tables as much as you can.
You can also have a master table with all the structures and their common properties.

Another approach is to have xml type column, which can be as flexible as any xml or you can apply schema to enforce data integrity.

You basically need to balance between those two options.

Database design also depends on your DBMS capabilities. For example, not all databases support XML types.

[Edit]

The table structure could be like this:

Structure

• Id - auto-generated PK
• Name
• Type
• Color

Bridge

• Id
• StructreId references Structure(Id)
• Length
• MaterialType

Wall

• Id
• StructreId references Structure(Id)
• Height
• Thikness
• MaterialType

Your case looks like an instance of the gen-spec design pattern. The question of how to design relational tables to reflect gen-spec has been brought up before.

See one thread.

I think you may have made a small error in the way you set up the dependancies in Access. Seems you have one-to-one where you need one-to-zero-or-one.