Design advice needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im designing a database application that will be built using a SQL Server
backend but I couldnt see a managed newsgroup for sql server table design so,
as Im sure the advice will be the same irrespective of whether I use Access
or SQL Server.

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.

I'd appreciate some opinions on this
 
tbl_Project
ProjectID (PK)
CustomerID

tbl_Structure
StructureID (PK)
StructureName
MemoText [this will be a full text description of a particular type of
structure, and could include which fields in tbl_StructureProjects will be
used in describing this structure type]
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID
Length
Width
Height
WidthAttachedStructure
Backboarding
LiftHeight
NumberOfLifts
etc., etc.

Each Project could then be associated with any number of structures. All
structure types will be described in tbl_Structure. For any particular
Project/Structure, just use the fields that are needed to describe that
particular structure. When constructing forms and reports, if you are
filtering for a particular type of structure, you can use just the fields
that you need for that structure.
 
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
properties of the 13 diff structure types. Most structures will only use
about 10 fields resulting in 40 empty fields per record.

The DB will grow by approx 150000 records per year so that seems like a lot
of wasted space - but it would be very simple to program

mnature said:
tbl_Project
ProjectID (PK)
CustomerID

tbl_Structure
StructureID (PK)
StructureName
MemoText [this will be a full text description of a particular type of
structure, and could include which fields in tbl_StructureProjects will be
used in describing this structure type]
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID
Length
Width
Height
WidthAttachedStructure
Backboarding
LiftHeight
NumberOfLifts
etc., etc.

Each Project could then be associated with any number of structures. All
structure types will be described in tbl_Structure. For any particular
Project/Structure, just use the fields that are needed to describe that
particular structure. When constructing forms and reports, if you are
filtering for a particular type of structure, you can use just the fields
that you need for that structure.

Terry Holland said:
Im designing a database application that will be built using a SQL Server
backend but I couldnt see a managed newsgroup for sql server table design so,
as Im sure the advice will be the same irrespective of whether I use Access
or SQL Server.

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.

I'd appreciate some opinions on this
Click to expand...
Click to expand...
 
StructureProperties will have only as many records as you put into it. One
approach is to base a form (frmProject) on tblProject and a subform
(fsubStructureProject) on tblStructureProjects. A combo box on
fsubStructureProject uses tblStructure as its row source, and is bound to
StructureID. You could make fsubStructureProject a continuous form so that
you can see a continuous listing of records.

Terry Holland said:
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
properties of the 13 diff structure types. Most structures will only use
about 10 fields resulting in 40 empty fields per record.

The DB will grow by approx 150000 records per year so that seems like a
lot
of wasted space - but it would be very simple to program

mnature said:
tbl_Project
ProjectID (PK)
CustomerID

tbl_Structure
StructureID (PK)
StructureName
MemoText [this will be a full text description of a particular type of
structure, and could include which fields in tbl_StructureProjects will
be
used in describing this structure type]
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID
Length
Width
Height
WidthAttachedStructure
Backboarding
LiftHeight
NumberOfLifts
etc., etc.

Each Project could then be associated with any number of structures. All
structure types will be described in tbl_Structure. For any particular
Project/Structure, just use the fields that are needed to describe that
particular structure. When constructing forms and reports, if you are
filtering for a particular type of structure, you can use just the fields
that you need for that structure.

Terry Holland said:
Im designing a database application that will be built using a SQL
Server
backend but I couldnt see a managed newsgroup for sql server table
design so,
as Im sure the advice will be the same irrespective of whether I use
Access
or SQL Server.

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.

I'd appreciate some opinions on this
Click to expand...
Click to expand...
Click to expand...
 
The advice Im asking for is how to struture my tables. Should I, as mnature
suggests, have a single StructureProperties table with a field for each of
the properties that will occur in all of my structure types, resulting in a
total of about
50 fields or should I have a table for each of the differnt structure types
each of which only contain the fields relevent to that particular structure
type ie

my suggestion:

tbl_Independent
==========
IndependentID (PK)
Length
Height
NumberOfLifts
....

tbl_ProtectionFan
===========
ProtFanID (PK)
HeightOfLifts
WidthOfAttachedStructure
BackBoarding


Or mnature suggestion:

tbl_Structure
========
StructureID (PK)
StructureType
Length
Height
NumberOfLifts
HeightOfLifts
WidthOfAttachedStructure
BackBoarding

knowing that for all structures of type Independent, only the fileds Length,
Height and NumberOfLifts will contain data and for all structures of type
ProtectionFan only the fields HeightOfLifts, WidthOfAttachedStructure and
BackBoarding fields will contain data.

This example is a small example to illustrate my point. In reality I would
need a structure table containing approx 50 fields to cover all of the
properties for all of the different stucture types if I go for the single
table structure, most of which will contain no data for individual records
(as most of the fields will be irrelevant to the particular structure type)

I will not be using Access for developing my application. I will be using a
SQL Server backend with an ASP.Net front end. Im only posting in this
newsgroup as there does not seem to be a managed newsgroup relating to SQL
Server table design.


BruceM said:
StructureProperties will have only as many records as you put into it. One
approach is to base a form (frmProject) on tblProject and a subform
(fsubStructureProject) on tblStructureProjects. A combo box on
fsubStructureProject uses tblStructure as its row source, and is bound to
StructureID. You could make fsubStructureProject a continuous form so that
you can see a continuous listing of records.

Terry Holland said:
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
properties of the 13 diff structure types. Most structures will only use
about 10 fields resulting in 40 empty fields per record.

The DB will grow by approx 150000 records per year so that seems like a
lot
of wasted space - but it would be very simple to program

mnature said:
tbl_Project
ProjectID (PK)
CustomerID

tbl_Structure
StructureID (PK)
StructureName
MemoText [this will be a full text description of a particular type of
structure, and could include which fields in tbl_StructureProjects will
be
used in describing this structure type]
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID
Length
Width
Height
WidthAttachedStructure
Backboarding
LiftHeight
NumberOfLifts
etc., etc.

Each Project could then be associated with any number of structures. All
structure types will be described in tbl_Structure. For any particular
Project/Structure, just use the fields that are needed to describe that
particular structure. When constructing forms and reports, if you are
filtering for a particular type of structure, you can use just the fields
that you need for that structure.

:

Im designing a database application that will be built using a SQL
Server
backend but I couldnt see a managed newsgroup for sql server table
design so,
as Im sure the advice will be the same irrespective of whether I use
Access
or SQL Server.

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.

I'd appreciate some opinions on this
Click to expand...
Click to expand...
Click to expand...
 
Thought of a rather elegant way to do this:

tbl_Project
ProjectID (PK)

tbl_Structure
StructureID (PK)
StructureName
MemoText
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID

tbl_StructureProperties [List all possible properties in this table]
StructurePropertiesID (PK)
PropertyName [Descriptive, such as Length, Width, Number of Lifts]
UnitsType [Number, Feet, Pounds]

tbl_StructureProjectsProperties [Ties together StructureProjects and
Properties]
StructureProjectsPropertiesID (PK)
StructureProjectsID
StructurePropertiesID
Units [This will be the actual number that corresponds to the UnitsType]

See if this makes sense to you. You would have a form where you select the
particular structure. The MemoText of that structure would indicate which
properties you need to use. You would use a subform to select those
properties, and fill in the units.

Terry Holland said:
The advice Im asking for is how to struture my tables. Should I, as mnature
suggests, have a single StructureProperties table with a field for each of
the properties that will occur in all of my structure types, resulting in a
total of about
50 fields or should I have a table for each of the differnt structure types
each of which only contain the fields relevent to that particular structure
type ie

my suggestion:

tbl_Independent
==========
IndependentID (PK)
Length
Height
NumberOfLifts
...

tbl_ProtectionFan
===========
ProtFanID (PK)
HeightOfLifts
WidthOfAttachedStructure
BackBoarding


Or mnature suggestion:

tbl_Structure
========
StructureID (PK)
StructureType
Length
Height
NumberOfLifts
HeightOfLifts
WidthOfAttachedStructure
BackBoarding

knowing that for all structures of type Independent, only the fileds Length,
Height and NumberOfLifts will contain data and for all structures of type
ProtectionFan only the fields HeightOfLifts, WidthOfAttachedStructure and
BackBoarding fields will contain data.

This example is a small example to illustrate my point. In reality I would
need a structure table containing approx 50 fields to cover all of the
properties for all of the different stucture types if I go for the single
table structure, most of which will contain no data for individual records
(as most of the fields will be irrelevant to the particular structure type)

I will not be using Access for developing my application. I will be using a
SQL Server backend with an ASP.Net front end. Im only posting in this
newsgroup as there does not seem to be a managed newsgroup relating to SQL
Server table design.


BruceM said:
StructureProperties will have only as many records as you put into it. One
approach is to base a form (frmProject) on tblProject and a subform
(fsubStructureProject) on tblStructureProjects. A combo box on
fsubStructureProject uses tblStructure as its row source, and is bound to
StructureID. You could make fsubStructureProject a continuous form so that
you can see a continuous listing of records.

Terry Holland said:
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
properties of the 13 diff structure types. Most structures will only use
about 10 fields resulting in 40 empty fields per record.

The DB will grow by approx 150000 records per year so that seems like a
lot
of wasted space - but it would be very simple to program

:

tbl_Project
ProjectID (PK)
CustomerID

tbl_Structure
StructureID (PK)
StructureName
MemoText [this will be a full text description of a particular type of
structure, and could include which fields in tbl_StructureProjects will
be
used in describing this structure type]
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID
Length
Width
Height
WidthAttachedStructure
Backboarding
LiftHeight
NumberOfLifts
etc., etc.

Each Project could then be associated with any number of structures. All
structure types will be described in tbl_Structure. For any particular
Project/Structure, just use the fields that are needed to describe that
particular structure. When constructing forms and reports, if you are
filtering for a particular type of structure, you can use just the fields
that you need for that structure.

:

Im designing a database application that will be built using a SQL
Server
backend but I couldnt see a managed newsgroup for sql server table
design so,
as Im sure the advice will be the same irrespective of whether I use
Access
or SQL Server.

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.

I'd appreciate some opinions on this
Click to expand...
Click to expand...
Click to expand...
 
This is along the lines that I was thinking of. I have a couple of problems
with this

1) The Units field in tbl_StructureProjectsProperties would need to be
VarChar because some of the fields will be numeric and some will be text.
This will result in late binding in my asp application resulting in a
performance hit
2) The increase in the number of relationships would result in reduced
performance

What I like about it is that if at some time I need to store information on
other properties, it would be as simple as adding rows to db rather than
changing table design

mnature said:
Thought of a rather elegant way to do this:

tbl_Project
ProjectID (PK)

tbl_Structure
StructureID (PK)
StructureName
MemoText
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID

tbl_StructureProperties [List all possible properties in this table]
StructurePropertiesID (PK)
PropertyName [Descriptive, such as Length, Width, Number of Lifts]
UnitsType [Number, Feet, Pounds]

tbl_StructureProjectsProperties [Ties together StructureProjects and
Properties]
StructureProjectsPropertiesID (PK)
StructureProjectsID
StructurePropertiesID
Units [This will be the actual number that corresponds to the UnitsType]

See if this makes sense to you. You would have a form where you select the
particular structure. The MemoText of that structure would indicate which
properties you need to use. You would use a subform to select those
properties, and fill in the units.

Terry Holland said:
The advice Im asking for is how to struture my tables. Should I, as mnature
suggests, have a single StructureProperties table with a field for each of
the properties that will occur in all of my structure types, resulting in a
total of about
50 fields or should I have a table for each of the differnt structure types
each of which only contain the fields relevent to that particular structure
type ie

my suggestion:

tbl_Independent
==========
IndependentID (PK)
Length
Height
NumberOfLifts
...

tbl_ProtectionFan
===========
ProtFanID (PK)
HeightOfLifts
WidthOfAttachedStructure
BackBoarding


Or mnature suggestion:

tbl_Structure
========
StructureID (PK)
StructureType
Length
Height
NumberOfLifts
HeightOfLifts
WidthOfAttachedStructure
BackBoarding

knowing that for all structures of type Independent, only the fileds Length,
Height and NumberOfLifts will contain data and for all structures of type
ProtectionFan only the fields HeightOfLifts, WidthOfAttachedStructure and
BackBoarding fields will contain data.

This example is a small example to illustrate my point. In reality I would
need a structure table containing approx 50 fields to cover all of the
properties for all of the different stucture types if I go for the single
table structure, most of which will contain no data for individual records
(as most of the fields will be irrelevant to the particular structure type)

I will not be using Access for developing my application. I will be using a
SQL Server backend with an ASP.Net front end. Im only posting in this
newsgroup as there does not seem to be a managed newsgroup relating to SQL
Server table design.


BruceM said:
StructureProperties will have only as many records as you put into it. One
approach is to base a form (frmProject) on tblProject and a subform
(fsubStructureProject) on tblStructureProjects. A combo box on
fsubStructureProject uses tblStructure as its row source, and is bound to
StructureID. You could make fsubStructureProject a continuous form so that
you can see a continuous listing of records.

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
properties of the 13 diff structure types. Most structures will only use
about 10 fields resulting in 40 empty fields per record.

The DB will grow by approx 150000 records per year so that seems like a
lot
of wasted space - but it would be very simple to program

:

tbl_Project
ProjectID (PK)
CustomerID

tbl_Structure
StructureID (PK)
StructureName
MemoText [this will be a full text description of a particular type of
structure, and could include which fields in tbl_StructureProjects will
be
used in describing this structure type]
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID
Length
Width
Height
WidthAttachedStructure
Backboarding
LiftHeight
NumberOfLifts
etc., etc.

Each Project could then be associated with any number of structures. All
structure types will be described in tbl_Structure. For any particular
Project/Structure, just use the fields that are needed to describe that
particular structure. When constructing forms and reports, if you are
filtering for a particular type of structure, you can use just the fields
that you need for that structure.

:

Im designing a database application that will be built using a SQL
Server
backend but I couldnt see a managed newsgroup for sql server table
design so,
as Im sure the advice will be the same irrespective of whether I use
Access
or SQL Server.

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.

I'd appreciate some opinions on this
Click to expand...
Click to expand...
Click to expand...
 
1) The Units field in tbl_StructureProjectsProperties would need to be
VarChar because some of the fields will be numeric and some will be text.
This will result in late binding in my asp application resulting in a
performance hit
Click to expand...

Use both UnitsNumber and UnitsText fields, then fill in whichever is
appropriate. A little wasted space will result in better performance.
2) The increase in the number of relationships would result in reduced
performance
Click to expand...

Performance can be measured in a number of ways. However, I believe that
having just five tables will enhance performance, as opposed to having a
table for each type of structure.
What I like about it is that if at some time I need to store information on
other properties, it would be as simple as adding rows to db rather than
changing table design
Click to expand...

That is the real value of a normalized database. Flexibility and
sustainability.
mnature said:
Thought of a rather elegant way to do this:

tbl_Project
ProjectID (PK)

tbl_Structure
StructureID (PK)
StructureName
MemoText
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID

tbl_StructureProperties [List all possible properties in this table]
StructurePropertiesID (PK)
PropertyName [Descriptive, such as Length, Width, Number of Lifts]
UnitsType [Number, Feet, Pounds]

tbl_StructureProjectsProperties [Ties together StructureProjects and
Properties]
StructureProjectsPropertiesID (PK)
StructureProjectsID
StructurePropertiesID
Units [This will be the actual number that corresponds to the UnitsType]

See if this makes sense to you. You would have a form where you select the
particular structure. The MemoText of that structure would indicate which
properties you need to use. You would use a subform to select those
properties, and fill in the units.

Terry Holland said:
The advice Im asking for is how to struture my tables. Should I, as mnature
suggests, have a single StructureProperties table with a field for each of
the properties that will occur in all of my structure types, resulting in a
total of about
50 fields or should I have a table for each of the differnt structure types
each of which only contain the fields relevent to that particular structure
type ie

my suggestion:

tbl_Independent
==========
IndependentID (PK)
Length
Height
NumberOfLifts
...

tbl_ProtectionFan
===========
ProtFanID (PK)
HeightOfLifts
WidthOfAttachedStructure
BackBoarding


Or mnature suggestion:

tbl_Structure
========
StructureID (PK)
StructureType
Length
Height
NumberOfLifts
HeightOfLifts
WidthOfAttachedStructure
BackBoarding

knowing that for all structures of type Independent, only the fileds Length,
Height and NumberOfLifts will contain data and for all structures of type
ProtectionFan only the fields HeightOfLifts, WidthOfAttachedStructure and
BackBoarding fields will contain data.

This example is a small example to illustrate my point. In reality I would
need a structure table containing approx 50 fields to cover all of the
properties for all of the different stucture types if I go for the single
table structure, most of which will contain no data for individual records
(as most of the fields will be irrelevant to the particular structure type)

I will not be using Access for developing my application. I will be using a
SQL Server backend with an ASP.Net front end. Im only posting in this
newsgroup as there does not seem to be a managed newsgroup relating to SQL
Server table design.


:

StructureProperties will have only as many records as you put into it. One
approach is to base a form (frmProject) on tblProject and a subform
(fsubStructureProject) on tblStructureProjects. A combo box on
fsubStructureProject uses tblStructure as its row source, and is bound to
StructureID. You could make fsubStructureProject a continuous form so that
you can see a continuous listing of records.

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
properties of the 13 diff structure types. Most structures will only use
about 10 fields resulting in 40 empty fields per record.

The DB will grow by approx 150000 records per year so that seems like a
lot
of wasted space - but it would be very simple to program

:

tbl_Project
ProjectID (PK)
CustomerID

tbl_Structure
StructureID (PK)
StructureName
MemoText [this will be a full text description of a particular type of
structure, and could include which fields in tbl_StructureProjects will
be
used in describing this structure type]
LabourCost
MaterialCost

tbl_StructureProjects
StructureProjectsID (PK)
ProjectID
StructureID
Length
Width
Height
WidthAttachedStructure
Backboarding
LiftHeight
NumberOfLifts
etc., etc.

Each Project could then be associated with any number of structures. All
structure types will be described in tbl_Structure. For any particular
Project/Structure, just use the fields that are needed to describe that
particular structure. When constructing forms and reports, if you are
filtering for a particular type of structure, you can use just the fields
that you need for that structure.

:

Im designing a database application that will be built using a SQL
Server
backend but I couldnt see a managed newsgroup for sql server table
design so,
as Im sure the advice will be the same irrespective of whether I use
Access
or SQL Server.

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.

I'd appreciate some opinions on this
Click to expand...
Click to expand...
Click to expand...
 
Back
Top