Table Design

Hello guys,

I have decided to do a small home project, to learn some new things.
My idea is a home shopping list generator. I'd like some advice on my
initial table design, as we all know if that's wrong - the project is
going to be difficult.

So, I have started with the basic table structure. First though, my
requirements.

The system stores details of grocery items in the fridge/cupboard of a
kitchen.

An example of an item:

Type: Dairy
Name: Milk
Varient: Full Cream
Manufacturer: Coles
Volume: 3.0 Litre
Barcode: 987298727

Simple enough, but remember, I can also have the same product, from a
different manufacturer:

Type: Dairy
Name: Milk
Varient: Full Cream
Manufacturer: Pick n' Pay
Volume: 3.0 Litre
Barcode: 893276423

I can also have the same product, from the same manufacturer, but a
different volume:

Type: Dairy
Name: Milk
Varient: Full Cream
Manufacturer: Coles
Volume: 1.0 Litre
Barcode: 387648736

And I can have a different variation of Milk:

Type: Dairy
Name: Milk
Varient: Low Fat 2%
Manufacturer: Coles
Volume: 3.0 Litre
Barcode: 873648762

The main output of the application is a shopping list. So, when we go
to the shop, we print the list, and it would list the item as:

Full Cream Milk
Low Fat 2% Milk

Very simple so far, but it's the table design, and normalisation I am
trying to ensure I get right. So, My table structure is listed below
to see what I am trying.

Product table holds the base product details. For example, 'Milk'.
Milk can then have variations, such as 'Full Cream' and 'Low Fat'. So
the 'ProductLine' tables holds the details of the product. The volume,
the barcode, the manufacturer.

I THINK the Manufacturer should not be in this table. I think it un-
normalises the table.. If I have it there, I can have the same product
details, but only the ManufacturerID is different. Should I have,
maybe, a 'ManufacturerProductLine' many to many link table? Then
remove ManufacturerID from from the ProductLine?

Here's the DDL. It should work. I'd like opinions on my initial
design, and maybe any hints, but also, how to handle the Product Line
table.

CREATE TABLE dbo.[imc_VolumeType]
(
id INT NOT NULL IDENTITY(1,1),
shortname VARCHAR(10) NOT NULL,
longname VARCHAR(30) NOT NULL,
baseunitid INT NOT NULL,
baseunitmultiplier FLOAT NOT NULL,
deleted DATETIME,

CONSTRAINT pk_imc_volumetype PRIMARY KEY NONCLUSTERED (id),
CONSTRAINT uq_imc_volumetype UNIQUE (shortname, deleted),
CONSTRAINT uq_imc_volumetype2 UNIQUE (longname, deleted),
CONSTRAINT fk_volumetype_volumetype FOREIGN KEY (baseunitid)
REFERENCES dbo.imc_VolumeType (id)
)
GO

INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'ml',
/* longname - VARCHAR(30) */ 'Milliliter',
/* baseunitid - INT */ 1,
1,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'g',
/* longname - VARCHAR(30) */ 'Gram',
/* baseunitid - INT */ 2,
1,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'L',
/* longname - VARCHAR(30) */ 'Litre',
/* baseunitid - INT */ 1,
1000,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'Kg',
/* longname - VARCHAR(30) */ 'Kilogram',
/* baseunitid - INT */ 2,
1000,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'Unt',
/* longname - VARCHAR(30) */ 'Unit',
/* baseunitid - INT */ 5,
1,
/* deleted - DATETIME */ NULL )

GO

CREATE TABLE dbo.[imc_Manufacturer]
(
id INT NOT NULL IDENTITY(1,1),
description VARCHAR(30) NOT NULL,
deleted DATETIME

CONSTRAINT pk_imc_manufacturer PRIMARY KEY NONCLUSTERED (id),
CONSTRAINT uq_imc_manufacturer UNIQUE (description, deleted)
)
GO

INSERT INTO dbo.[imc_Manufacturer]
(description) VALUES ('Coles')
INSERT INTO dbo.[imc_Manufacturer]
(description) VALUES ('Saniterium')

GO

CREATE TABLE dbo.[imc_ProductCategory]
(
id INT NOT NULL IDENTITY(1,1),
description VARCHAR(30) NOT NULL,
deleted DATETIME

CONSTRAINT pk_imc_productcategory PRIMARY KEY (id),
CONSTRAINT uq_imc_productcategory UNIQUE (description, deleted)
)
GO

INSERT INTO dbo.[imc_ProductCategory]
(description) VALUES ('Dairy')
INSERT INTO dbo.[imc_ProductCategory]
(description) VALUES ('Cereal')
INSERT INTO dbo.[imc_ProductCategory]
(description) VALUES ('Tinner')
GO

CREATE TABLE dbo.[imc_Product]
(
id INT NOT NULL IDENTITY(1,1),
productcategoryid INT NOT NULL,
description VARCHAR(50) NOT NULL,
deleted DATETIME

CONSTRAINT pk_imc_product PRIMARY KEY (id),
CONSTRAINT fk_imc_product_productcategory FOREIGN KEY
(productcategoryid) REFERENCES dbo.[imc_ProductCategory] (id),
CONSTRAINT uq_imc_product UNIQUE (description, deleted)
)
GO

INSERT INTO dbo.[imc_Product] (
description,
productcategoryid,
deleted
) VALUES (
/* description - VARCHAR(50) */ 'Milk',
1,
NULL )

INSERT INTO dbo.[imc_Product] (
description,
productcategoryid,
deleted
) VALUES (
/* description - VARCHAR(50) */ 'Weetbox',
2,
NULL )

GO

CREATE TABLE dbo.[imc_ProductLine]
(
id INT NOT NULL IDENTITY(1,1),
productid INT NOT NULL,
variation VARCHAR(30),
manufacturerid INT NOT NULL,
volume FLOAT NOT NULL,
volumetypeid INT NOT NULL,
deleted DATETIME


CONSTRAINT pk_imc_productline PRIMARY KEY (id),
CONSTRAINT uq_imc_productline UNIQUE (productid, variation, volume,
volumetypeid, deleted),
CONSTRAINT fk_imc_productline_product FOREIGN KEY (productid)
REFERENCES dbo.[imc_Product] (id),
CONSTRAINT fk_imc_productline_volumetype FOREIGN KEY (volumetypeid)
REFERENCES dbo.[imc_VolumeType] (id),
CONSTRAINT fk_imc_productline_manufacturer FOREIGN KEY
(manufacturerid) REFERENCES dbo.[imc_Manufacturer] (id)
)
GO

INSERT INTO dbo.imc_ProductLine (
productid,
variation,
manufacturerid,
volume,
volumetypeid,
deleted
) VALUES (
1,
'Full Cream',
1,
3,
3,
NULL )
GO

INSERT INTO dbo.imc_ProductLine (
productid,
variation,
manufacturerid,
volume,
volumetypeid,
deleted
) VALUES (
1,
'2%',
1,
3,
3,
NULL )

INSERT INTO dbo.imc_ProductLine (
productid,
variation,
manufacturerid,
volume,
volumetypeid,
deleted
) VALUES (
2,
'',
2,
1.35,
4,
NULL )
GO

// Example Query to get Products

SELECT
pc.description,
m.description,
p.description,
pl.variation,
pl.volume,
vt.longname + ' (' + vt.shortname + ')' AS VolumeDescription
FROM dbo.[imc_ProductLine] pl
INNER JOIN dbo.[imc_Product] p ON p.id = pl.productid
INNER JOIN dbo.[imc_ProductCategory] pc ON pc.id =
p.ProductCategoryID
INNER JOIN dbo.[imc_VolumeType] vt ON vt.id = pl.volumetypeid
INNER JOIN dbo.[imc_Manufacturer] m ON m.id = pl.manufacturerid
WHERE pl.deleted IS NULL

Thanks Sloan.

I have an ERD on a bit of paper here, and it's extremely useful. I'm
really trying to get advise on my design though. The scheme I have
supplied basically matches my design. I've actually since removed
Manufacturer from ProductLine, and created a link table. I THINK this
is better, but will make the GUI design interesting. But I think the
data would be more normalised. Here's the updated schema.

(PS: Have a good weekend! Here in Australia, we're already way into
Saturday.. )


CREATE TABLE dbo.[imc_User]
(
id INT NOT NULL IDENTITY(1,1),
username VARCHAR(15) NOT NULL,
firstname VARCHAR(30),
surname VARCHAR(30),
email VARCHAR(100),
registered DATETIME DEFAULT(GETDATE()),
deleted DATETIME

CONSTRAINT pk_imc_user PRIMARY KEY NONCLUSTERED (id),
CONSTRAINT uq_imc_user UNIQUE (username, deleted)
)
GO

CREATE TABLE dbo.[imc_VolumeType]
(
id INT NOT NULL IDENTITY(1,1),
shortname VARCHAR(10) NOT NULL,
longname VARCHAR(30) NOT NULL,
baseunitid INT NOT NULL,
baseunitmultiplier FLOAT NOT NULL,
deleted DATETIME,

CONSTRAINT pk_imc_volumetype PRIMARY KEY NONCLUSTERED (id),
CONSTRAINT uq_imc_volumetype UNIQUE (shortname, deleted),
CONSTRAINT uq_imc_volumetype2 UNIQUE (longname, deleted),
CONSTRAINT fk_volumetype_volumetype FOREIGN KEY (baseunitid)
REFERENCES dbo.imc_VolumeType (id)
)
GO

INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'ml',
/* longname - VARCHAR(30) */ 'Milliliter',
/* baseunitid - INT */ 1,
1,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'g',
/* longname - VARCHAR(30) */ 'Gram',
/* baseunitid - INT */ 2,
1,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'L',
/* longname - VARCHAR(30) */ 'Litre',
/* baseunitid - INT */ 1,
1000,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'Kg',
/* longname - VARCHAR(30) */ 'Kilogram',
/* baseunitid - INT */ 2,
1000,
/* deleted - DATETIME */ NULL )


INSERT INTO dbo.imc_VolumeType (
shortname,
longname,
baseunitid,
baseunitmultiplier,
deleted
) VALUES (
/* shortname - VARCHAR(10) */ 'Unt',
/* longname - VARCHAR(30) */ 'Unit',
/* baseunitid - INT */ 5,
1,
/* deleted - DATETIME */ NULL )


GO

CREATE TABLE dbo.[imc_Manufacturer]
(
id INT NOT NULL IDENTITY(1,1),
description VARCHAR(30) NOT NULL,
deleted DATETIME

CONSTRAINT pk_imc_manufacturer PRIMARY KEY NONCLUSTERED (id),
CONSTRAINT uq_imc_manufacturer UNIQUE (description, deleted)
)
GO

INSERT INTO dbo.[imc_Manufacturer]
(description) VALUES ('Coles')
INSERT INTO dbo.[imc_Manufacturer]
(description) VALUES ('Saniterium')
INSERT INTO dbo.[imc_Manufacturer]
(description) VALUES ('Pick and Pay')

GO

CREATE TABLE dbo.[imc_ProductCategory]
(
id INT NOT NULL IDENTITY(1,1),
description VARCHAR(30) NOT NULL,
deleted DATETIME

CONSTRAINT pk_imc_productcategory PRIMARY KEY (id),
CONSTRAINT uq_imc_productcategory UNIQUE (description, deleted)
)
GO

INSERT INTO dbo.[imc_ProductCategory]
(description) VALUES ('Dairy')
INSERT INTO dbo.[imc_ProductCategory]
(description) VALUES ('Cereal')
INSERT INTO dbo.[imc_ProductCategory]
(description) VALUES ('Tinner')
GO


CREATE TABLE dbo.[imc_Product]
(
id INT NOT NULL IDENTITY(1,1),
productcategoryid INT NOT NULL,
description VARCHAR(50) NOT NULL,
deleted DATETIME

CONSTRAINT pk_imc_product PRIMARY KEY (id),
CONSTRAINT fk_imc_product_productcategory FOREIGN KEY
(productcategoryid) REFERENCES dbo.[imc_ProductCategory] (id),
CONSTRAINT uq_imc_product UNIQUE (description, deleted)
)
GO

INSERT INTO dbo.[imc_Product] (
description,
productcategoryid,
deleted
) VALUES (
/* description - VARCHAR(50) */ 'Milk',
1,
NULL )

INSERT INTO dbo.[imc_Product] (
description,
productcategoryid,
deleted
) VALUES (
/* description - VARCHAR(50) */ 'Weetbox',
2,
NULL )

GO

CREATE TABLE dbo.[imc_ProductLine]
(
id INT NOT NULL IDENTITY(1,1),
productid INT NOT NULL,
variation VARCHAR(30),
volume FLOAT NOT NULL,
volumetypeid INT NOT NULL,
deleted DATETIME


CONSTRAINT pk_imc_productline PRIMARY KEY (id),
CONSTRAINT uq_imc_productline UNIQUE (productid, variation, volume,
volumetypeid, deleted),
CONSTRAINT fk_imc_productline_product FOREIGN KEY (productid)
REFERENCES dbo.[imc_Product] (id),
CONSTRAINT fk_imc_productline_volumetype FOREIGN KEY (volumetypeid)
REFERENCES dbo.[imc_VolumeType] (id)
)
GO

CREATE TABLE dbo.[imc_ProductLineManufacturer]
(
productlineid INT NOT NULL,
manufacturerid INT NOT NULL,
deleted DATETIME

CONSTRAINT pk_ProductLineManufacturer PRIMARY KEY (productlineid,
manufacturerid),
CONSTRAINT fk_ProductLineManufacturerP FOREIGN KEY (productlineid)
REFERENCES dbo.[imc_ProductLine] (id),
CONSTRAINT fk_ProductLineManufacturerM FOREIGN KEY (manufacturerid)
REFERENCES dbo.[imc_Manufacturer] (id)

)
GO

INSERT INTO dbo.imc_ProductLine (
productid,
variation,
volume,
volumetypeid,
deleted
) VALUES (
1,
'Full Cream',
3,
3,
NULL )
GO

INSERT INTO dbo.imc_ProductLine (
productid,
variation,
volume,
volumetypeid,
deleted
) VALUES (
1,
'2%',
3,
3,
NULL )

INSERT INTO dbo.imc_ProductLine (
productid,
variation,
volume,
volumetypeid,
deleted
) VALUES (
2,
'',
1.3,
4,
NULL )

GO

INSERT INTO dbo.[imc_ProductLineManufacturer] (
productlineid,
manufacturerid,
deleted
) VALUES (
/* productlineid - INT */ 1,
/* manufacturerid - INT */ 1,
NULL )

INSERT INTO dbo.[imc_ProductLineManufacturer] (
productlineid,
manufacturerid,
deleted
) VALUES (
/* productlineid - INT */ 1,
/* manufacturerid - INT */ 3,
NULL )

INSERT INTO dbo.[imc_ProductLineManufacturer] (
productlineid,
manufacturerid,
deleted
) VALUES (
/* productlineid - INT */ 2,
/* manufacturerid - INT */ 1,
NULL )

INSERT INTO dbo.[imc_ProductLineManufacturer] (
productlineid,
manufacturerid,
deleted
) VALUES (
/* productlineid - INT */ 2,
/* manufacturerid - INT */ 3,
NULL )

INSERT INTO dbo.[imc_ProductLineManufacturer] (
productlineid,
manufacturerid,
deleted
) VALUES (
/* productlineid - INT */ 3,
/* manufacturerid - INT */ 2,
NULL )

--
--CREATE PROC dbo.[sp_imc_Product_SEL]
-- productlineid INT
--AS
SELECT
pc.description,
m.description,
p.description,
pl.variation,
pl.volume,
vt.longname + ' (' + vt.shortname + ')' AS VolumeDescription
FROM dbo.[imc_ProductLine] pl
INNER JOIN dbo.[imc_ProductLineManufacturer] plm ON plm.productlineid
= pl.id
INNER JOIN dbo.[imc_Manufacturer] m ON m.id = plm.manufacturerid
INNER JOIN dbo.[imc_Product] p ON p.id = pl.productid
INNER JOIN dbo.[imc_ProductCategory] pc ON pc.id =
p.ProductCategoryID
INNER JOIN dbo.[imc_VolumeType] vt ON vt.id = pl.volumetypeid
WHERE pl.deleted IS NULL