Re: Table Design Advice

Thanks for response

I was previously posting in an MSAccess group so the datatypes are Access
specific. I will be creating db in SQL server though.

So the example seems to follow my first idea where there is a table
(Product) that contains fields that are common to all products and then a
seperate table for each of the different product types (Book & DVD), each of
them containing fields specific to their product type.

Could you explain why this is a better route than the follwoing scenarios as
both of these have been suggested in the Access newsgroup

a) having a Properties table as follows and add a row for each of the
properties

CREATE TABLE [dbo].[ProductProperty] (
[PropertyID] [int] IDENTITY (1, 1) NOT NULL ,
[sku_code] [int] NULL ,
[PropertyName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[PropertyValue] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

with the following records

PropertyID sku_code PropertyName PropertyValue
----------- ----------- ------------- --------------
1 2 Running Time 120
3 1 ISBN 999
4 2 Actor Morgan Freeman
5 1 Author Tolkien



b) having a Products table containing fields for all product types such as

CREATE TABLE [dbo].[products_alltypes] (
[sku_code] [int] NOT NULL ,
[product_type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[product_title] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[product_runningtime] [int] NULL ,
[product_actor] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[product_isbn] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[product_author] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[products_alltypes] ADD
CONSTRAINT [PK_products_alltypes] PRIMARY KEY CLUSTERED
(
[sku_code]
) ON [PRIMARY]
GO

containing the following records

sku_code product_type product_title product_runningtime product_actor
product_isbn product_author
----------- ------------ -------------- ------------------- ----------------
---------------- --------------
1 b lord of rings NULL NULL
999 Tolkien
2 d shawshank 120 Morgan Freeman
NULL NULL




"David Portas" wrote:

"Terry Holland" <TerryHolland@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8D2407FF-5B67-4C22-A49E-6B307AE4820E@xxxxxxxxxxxxxxxx
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




Here's a different example but hopefully it will give you the idea. Notice
that each product can only be of one or other type - the constraints prevent
any anomalies between the multiple type tables.

BTW. Are you sure you are using SQL Server? Your datatypes are not valid
ones in SQL.

CREATE TABLE products
(sku_code INTEGER NOT NULL
CONSTRAINT pk_products PRIMARY KEY,
product_type CHAR (1) NOT NULL /* Book, CD or DVD */
CONSTRAINT ck1_products CHECK (product_type IN ('B','D')),
product_title VARCHAR (50) NOT NULL
CONSTRAINT ak1_products UNIQUE,
CONSTRAINT ak2_products UNIQUE (sku_code,product_type));

CREATE TABLE books
(sku_code INTEGER NOT NULL
CONSTRAINT pk_books PRIMARY KEY,
CONSTRAINT fk_books_products
FOREIGN KEY (sku_code,product_type)
REFERENCES products (sku_code,product_type),
product_type CHAR (1) NOT NULL /* = Book ONLY */
DEFAULT ('B')
CONSTRAINT ck1_books CHECK (product_type = 'B'),
isbn CHAR (10) NOT NULL
CONSTRAINT ak1_books UNIQUE);

CREATE TABLE dvds
(sku_code INTEGER NOT NULL
CONSTRAINT pk_cds PRIMARY KEY,
CONSTRAINT fk_dvds_products
FOREIGN KEY (sku_code,product_type)
REFERENCES products (sku_code,product_type),
product_type CHAR (1) NOT NULL /* = DVD ONLY */
DEFAULT ('D')
CONSTRAINT ck1_dvds CHECK (product_type = 'D'),
running_time INTEGER NOT NULL);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--



.

Relevant Pages

  • Re: Indexes and primary keys, from Delaney
    ... > One of the issues here is that the Primary Key is a logical concept, ... > constraint mechanism to define a set of columns as the PK, ... > asking SQL Server to enforce the uniqueness of the columns. ... >> SSN and account number, ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Index Question
    ... you can do a create index with drop_existing on a primary key ... Columnist, SQL Server Professional ... primary key constraint this is not possible, ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)
Loading