I really need your judgement on a DB design

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

I really need your judgement on a DB design

Author

Topic

apurice
Starting Member

4 Posts

Posted - 2012-05-01 : 17:11:17

Hello,

I had an intense discussion with one of my coworkers on design of a database.

I created samples of 2 design proposals, _v1 is mine, _v2 is my coworker's proposal.


CREATE TABLE Colors_design_v1
(
	ID tinyint NOT NULL PRIMARY KEY,
	Name varchar(50) NOT NULL
)
GO

CREATE TABLE Sizes_design_v1
(
	ID tinyint NOT NULL PRIMARY KEY,
	Name varchar(50) NOT NULL
)
GO

CREATE TABLE Products_design_v1
(
	ID int NOT NULL PRIMARY KEY,
	Name varchar(50),
	ColorID tinyint NOT NULL REFERENCES Colors_design_v1(ID),
	SizeID tinyint NOT NULL REFERENCES Sizes_design_v1(ID) 
)
GO

INSERT INTO Colors_design_v1
SELECT 1, 'Black' UNION ALL
SELECT 2, 'White' UNION ALL
SELECT 3, 'Red'
go

INSERT INTO Sizes_design_v1
SELECT 1, 'XS' UNION ALL
SELECT 2, 'S' UNION ALL
SELECT 3, 'M' UNION ALL
SELECT 4, 'L' UNION ALL
SELECT 5, 'XL'
go

INSERT Products_design_v1
(ID, Name, ColorID, SizeID)
SELECT 1, 'T-Shirt', 2, 3 UNION ALL
SELECT 2, 'Coat', 1, 4 UNION ALL
SELECT 3, 'Tights', 3, 1


CREATE TABLE LabelTypes_design_v2
(
	ID tinyint NOT NULL PRIMARY KEY,
	Name varchar(50) NOT NULL
)
GO

CREATE TABLE Labels_design_v2
(
	ID tinyint NOT NULL PRIMARY KEY,
	Name varchar(50) NOT NULL
)
GO

CREATE TABLE Products_design_v2
(
	ID int NOT NULL PRIMARY KEY,
	Name varchar(50),
	LabelID tinyint NOT NULL REFERENCES Labels_design_v2(ID),
	LabelTypeID tinyint REFERENCES LabelTypes_design_v2(ID)
)
GO

INSERT INTO LabelTypes_design_v2
SELECT 1, 'Colors' UNION ALL
SELECT 2, 'Sizes'
go

INSERT INTO Labels_design_v2
(ID, Name)
SELECT 1, 'Black' UNION ALL
SELECT 2, 'White' UNION ALL
SELECT 3, 'Red' UNION ALL
SELECT 4, 'XS' UNION ALL
SELECT 5, 'S' UNION ALL
SELECT 6, 'M' UNION ALL
SELECT 7, 'L' UNION ALL
SELECT 8, 'XL'
go

INSERT Products_design_v2
(ID, Name, LabelID, LabelTypeID)
SELECT 1, 'T-Shirt', 2, 1 UNION ALL
SELECT 2, 'T-Shirt', 6, 2 UNION ALL
SELECT 3, 'Coat', 1, 1 UNION ALL
SELECT 4, 'Coat', 7, 2 UNION ALL
SELECT 5, 'Tights', 3, 1 UNION ALL
SELECT 6, 'Tights', 4, 2

After many years of development, I thought that my version is classic 3NF. But he insists that his version is better optimized, so I'm a little bit confused as he taught DB design in an university.

A second opinion will be highly appreciated.

Thanks,
Alex

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-01 : 17:46:39

The v2 design cannot properly enforce color and size integrity, for example:

INSERT Products_design_v2(ID, Name, LabelID, LabelTypeID)
VALUES(7,'Pants',1,2)

INSERT Products_design_v2(ID, Name, LabelID, LabelTypeID)
VALUES(8,'Pants',7,1)

SELECT p.*, l.Name Label, t.Name TYPE FROM Products_design_v2 p
INNER JOIN Labels_design_v2 l ON p.LabelID=l.ID
INNER JOIN LabelTypes_design_v2 t ON p.LabelTypeID=t.ID

You'll get L(arge) as a color and Black as a Size for the last 2 rows. Version 1 does not have this problem.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-05-01 : 20:53:50

Agreed V1 is the best. V2 is heading towards the dreaded EAV which is inappropriate here - you know you need a size and colour attribute.
Notice that in V2 you don't actually have the concept of a "small red t-shirt" or "large blue t-shirt". I'm guessing productID 1 and 2 are supposed to refer to the same item of clothing but there's no real way to be sure other than by it's name (t-shirt). How do you have more than one?

You might want to establish why it needs optimising in the first place. This V2 design invariably needs optimising (fixing) as soon as you need to do anything remotely complex.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-05-02 : 10:41:44

I am going to sit on the fence.

Your co-worker's design is very misleading in that it may streamline certain aspects but will add a lot of new issues.

For one, things are not clearly labelled in the V2 Database. Someone new wants to write SQL to list colors they would more likely look for the color table rather than Label Types and Labels.

On the other hand, V2 groups common things or consolidates them. For example, instead of writing several Stored Procedures like Color_Insert, Size_Insert, Style_Insert, etc... You'll only need one: Label_Insert.

boybawang
Starting Member

15 Posts

Posted - 2012-05-02 : 22:24:37

The database design has something to do with employers proposal. That's quite a good database design indeed.

apurice
Starting Member

4 Posts

Posted - 2012-05-04 : 16:29:58

Thank you for replies.

Would somebody mind to spent a few more minutes and take a look at the updated version of Design #2?

The reason of my questions is very simple. Besides the "contest" of the designs, which doesn't worry me too much, I'm trying to figure out an optimal way to store data as well.

The problem is that the database belongs to telecom industry and has hundreds of tables. Over 70% of them are "Color/Size"-like from my example and hold attributes of various products. So besides the fact that products themself are combinations of entities, entities themself have A LOT of attributes.

So, if there is a safe (in both development and performance meanings) way to reduce number of tables by combining attributes into a "generic" table(s), I would really take a such piece of advice.

Thank you very much again.


CREATE TABLE Labels_design_v2_2
(
	ID tinyint NOT NULL PRIMARY KEY,
	Name varchar(50) NOT NULL
)
GO

CREATE TABLE Products_design_v2_2
(
	ID int NOT NULL PRIMARY KEY,
	Name varchar(50)
)
GO

CREATE TABLE Product_Label_design_v2_2
(
	ProductID int NOT NULL REFERENCES Products_design_v2(ID),
	LabelID tinyint NOT NULL REFERENCES Labels_design_v2(ID)
)
GO
ALTER TABLE Product_Label_design_v2_2 ADD CONSTRAINT OK_Product_Label_design_v2_2 PRIMARY KEY (ProductID, LabelID)
GO

INSERT Labels_design_v2_2
(ID, Name)
SELECT 1, 'Black' UNION ALL
SELECT 2, 'White' UNION ALL
SELECT 3, 'Red' UNION ALL
SELECT 4, 'XS' UNION ALL
SELECT 5, 'S' UNION ALL
SELECT 6, 'M' UNION ALL
SELECT 7, 'L' UNION ALL
SELECT 8, 'XL'
go

INSERT Products_design_v2_2
(ID, Name)
SELECT 1, 'T-Shirt' UNION ALL
SELECT 2, 'Coat' UNION ALL
SELECT 3, 'Tights'
go

INSERT Product_Label_design_v2_2
(ProductID, LabelID)
SELECT 1, 2 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 3, 5

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-04 : 17:06:58

The problem with version 2, and any similar entity-value-attribute (EAV) design, is ensuring that the wrong attributes do not get assigned to the wrong product. For instance, with a telecom database, I imagine they would have phones and similar accessories (headphones, chargers, etc.) For these types of products, color may or may not apply, and size certainly wouldn't (at least not shirt or pants sizes). Without additional columns in the Products and Labels table you could end up with an Extra Large Red Phone Battery, which would make no sense.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-05-04 : 19:21:38

EAV is really horrible when implemented on top of SQL.

this is a good read: http://weblogs.sqlteam.com/davidm/articles/12117.aspx

elsasoft.org

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-05-13 : 20:13:35

One issue with your revised is now any type of select or using Crystal Reports is going to become difficult. If you wanted to write a query that returns something like:
ProductID Color Size
--------- ----- ----

... you are soon going to have a complicated query. Then you might come up with a view to simply things but that will be slower and you could argue if you need to make a view to simplify things, why not just use a single table with a single Record in the first place.

One possible solution to address that the wrong attributes do not get assigned to the wrong product would be:

Labels
------
LabelType (ie. Color)
Label (ie. White)
...

and then Product would be
ID
Name
ColorLabelType (always 'Color')
ColorLabel
SizeLabelType (always 'Size')
SizeLabel

Then LabelType/Label would be a composite primary key and so each attribute would make up an FK consisting of 2 columns. A sample Product record would be:

1 T-Shirt Color White Size XS

I wouldn't rule out any of these approaches that consolidate several tables into one. Here is a real-life example of how overkill the V1 approach can be. We had one table. This one table had 15 look-up code types associated with it (i.e. like you have Color, Size etc...). I was hoping we could have one Code Table with a Code Type column. Instead we made 15 different Code Tables.

We use the CRUD approach for stored procedures (i.e. Save, Delete, FetchList)- so instead of having 1 table with 5 Crud Stored Procedures, we had 15 x 5 = 75 Stored Procedures. Instead of having 1 form where we can view and update a Code list, we have 15 different forms.

And it gets better. Each of these Code Tables needed a Crystal Report to list all codes of a given code type. So instead of 1 Crystal Report, we needed 15.

I am not saying I am against the basic approach in V1. But I think you'll find pluses and minuses in both approaches.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources