Table Design Advice

Im designing a database application that will be built using a SQL
Serverbackend with an ASP.Net front end

My application needs to store data on a variety of different scaffold
structures. The different types of structures have different
propeties. For
example an Independent structure will have the following properties:
Length (Single)
Width (Single)
Height (Single)
NumberOfLifts (Integer)

A Protection will have the following properties
Length (Single)
Width (Single)
WidthOfAttachedStructure (Single)
BackBoarding (Boolean)
LiftHeight (Single)

There are thirteen different structure types all having a different set
of
properties. All of the structures have some common properties these
are
Qty (Integer)
LabourCost (Currency)
MaterialCost (Currency)

A project consists of any number of different types of structure. So,
for
example, a project could have 2 related Independent records, 5
Protection
Fans and so on.

Im wondering whether to create a Structure table with the following
fields

Structure
=========
StructureID
Qty (Integer)
LabourCost (Currency)
MaterialCost (Currency)


And then create a table for each of the different structure types.

The Independent table would have the following fields and would have a
one-to-one relationship to the Structure table

Independent
===========
StructureID (Long)
Length (Single)
Width (single)
Height (Single)
NumberOfLifts (Byte)

and Protection table would have the following fields and would have a
One-to-one relationship to the Structure table

ProtectionFan
=============
StructureID (Long)
Length (single)
Width (Single)
WidthOfAttachedStructure (Single)
BackBoarding (Boolean)
LiftHeight (Single)

Would this be a worthy setup or should I have smoething more like this.
A Structure table containing all of the common fields and a
StructureProperties table containing the following fields and having a
one-to-many relationship with the Structure table

StructureProperties
===================
PropertyID (Long)
StructureID (Long)
PropertyName (Text)
PropertyDataType (Byte)
PropertyValue (Text)

This table could then contain records

PropertyID: 1
StructureID: 1
PropertyName: Length
PropertyDataType: 1 (single)
PropertyValue: 100

PropertyID: 2
StructureID: 1
PropertyName: Width
PropertyDataType: 1 (single)
PropertyValue: 1.3

PropertyID: 3
StructureID: 1
PropertyName: Height
PropertyDataType: 1 (single)
PropertyValue: 12

PropertyID: 4
StructureID: 1
PropertyName: NumberOfLifts
PropertyDataType: 1 (integer)
PropertyValue: 6

This way strikes me as being more normalised, but more complicated to
program and perhaps with some performance issues.

Another option is to have a single StructureProperties table containing a
field for each of the properties that occurr in all of my structure types
ie

StructureProperties
===================
PropertyID (Integer) (PK)
StructureID (Integer) (FK)
StructureType
Length (Single)
Width (single)
Height (Single)
NumberOfLifts (Byte)
Length (single)
Width (Single)
WidthOfAttachedStructure (Single)
BackBoarding (Boolean)
LiftHeight (Single)
.....

Using this approach I would need to include approx 50 columns to cover all
structure properties but each structure type would only use approx 10 of the
fields (the ones that are relevent to the structure type). This is a
solution that was offered in tha AccessTableDesign newsgroup but it strikes
me as being quite wastefull and un-normalised - though It does seem to be the
easiest solution

I'd appreciate some opinions on this



.

Relevant Pages

  • Re: Design advice needed
    ... fsubStructureProject uses tblStructure as its row source, ... structure types will be described in tbl_Structure. ... A Protection will have the following properties ... MaterialCost (Currency) ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Design advice needed
    ... That seems a very simple solution but it will result in a lot of empty cells. ... The StructureProterties table will end up with approx 50 fields to cover all ... structure types will be described in tbl_Structure. ... MaterialCost (Currency) ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Design advice needed
    ... 50 fields or should I have a table for each of the differnt structure types ... WidthOfAttachedStructure ... SQL Server backend with an ASP.Net front end. ... MaterialCost (Currency) ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Design advice needed
    ... 50 fields or should I have a table for each of the differnt structure types ... WidthOfAttachedStructure ... SQL Server backend with an ASP.Net front end. ... MaterialCost (Currency) ...
    (microsoft.public.access.tablesdbdesign)
Loading