DB Design, storage of attributes

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

SQL Server 2000 Forums

SQL Server Development (2000)

DB Design, storage of attributes

Author

Topic

benoityip
Starting Member

14 Posts

Posted - 2003-06-10 : 05:52:11

When do we need store the column attributes vertically or horizontally, how does the design impact performance?

For example, suppose a table has over 300 columns, those attributes are of varchar type, would it make sense to store them horizontally?

Vertically:
CREATE TABLE ProductAttributes
{
ProductID int,
Name varchar(100),
ColumnType varchar(100),
Value varchar(8000)
}

Horizontally:
CREATE TABLE Product
{
ProductID int,
Column1 varchar(200),
Column2 varchar(200),
Column3 varchar(200),
Column4 varchar(200),
.....till column300
}

In horizontal db design
In select statment such as
SELECT c1, c300 FROM Product, how do the db SELECT the colmn names, is it scanning each column till the column name matches?? if u select c300, so it scan rom c1, c2... till c300?
In vertical design, you can index the column names..

I am bit confusing about their advantages and disadvantages..I don't want to break the 300 columns into several tables as well. What should I do, Thanks

Regards
Ben

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-10 : 09:39:57

Ben,

Do you know what data modeling is? Entity Realtionship Diagrams?

Normal forms?

Process modeling?

You're worrying (at least it seems to me) about physical implementation issues before you've modeled your business.

What are you trying to do...any metrics about the data would help as well.

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-10 : 10:34:38

As Brett asks, give us a list of 20-30 columns of your attributes. Are there dates involved? Or are some attributes repeating? is every single one truly unique and not dependant on something else?

For example, do you have columns like this:

Color1
Color2
Color3
Jan2000Value
Feb2000Value
Mar2000Value
...etc...

???

Does EVERY product have ALL 300 attributes, or is it dependant on a product type or something of that nature?

- Jeff

benoityip
Starting Member

14 Posts

Posted - 2003-06-10 : 11:25:53

Sorry to confuse you guys
My question is focusing on the physical db design rather than logical db design..

So I assume a product has 300 attributes with no null values, and I have also assumed that no veritcal partitioning is invloved..

So my question is:
if the sql is
SELECT column1, column300 FROM Product, how does the sql server locate column "column300", wouldn't it scan from column 1, column 2 till column 300 is reached, so the sql server is performing a column scan for matching column names???

Second question
Some ppl store the column values as data inside the table, so in the table

CREATE TABLE ProductAttributes
{
ProductID int,
Name varchar(100),
Value sql_variant
}

the table data will be

ProductId Name Value
1 Column1 Column1Value
2 Column2 Column2Value
3 Size 500m2
.....

So in Sql like "HOW TO RETRIVE, UPDATE AND INSERT Prouct data"

There are two ways
A: In horizontal table
Select * from product where productid=1
The result is
******************************************
productid column1 column2 column3 .........
1 columnvalue1 columnvalue2 columnvalue3
***************************

B:In Vertical
Select name,value from product where productid=1
The result is
******************************************
productid name value
1 column1 columnvalue1
1 column2 columnvalue2
1 column3 columnvalu3
***************************

What are the difference between the two?
I know the second one is often used in situations where we have dynamic product attributes..but I wanna know more

benoityip
Starting Member

14 Posts

Posted - 2003-06-10 : 11:40:52

My requirements
Use a db to store a property evaluation form submitted to bank cotaining over 300 fields, there is no repetable fields. Most of the fields are required...so not much null values are assumed..

I am currently store the column names values vertically, coze it allows dynamically adding fields, but I just wonder if we store them horizontally, what will happen? in terms of physical perfomance

benoityip
Starting Member

14 Posts

Posted - 2003-06-10 : 11:42:13

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-10 : 19:10:14

quote:
if the sql is
SELECT column1, column300 FROM Product, how does the sql server locate column "column300", wouldn't it scan from column 1, column 2 till column 300 is reached, so the sql server is performing a column scan for matching column names???

Technically, no, but with varchar data, there will be a little of that in some way. SQL Server will read an entire page of data regardless of the table structure or number of rows stored on the page. Once in RAM it will process the data, and the "scanning" required is infinitesimal in execution time.

Similarly, if you stored each attribute on a separate row, SQL Server will still read an entire page of data, it will just capture more rows per page. It will probably be a little faster because the row size will be smaller.

There is one practical issue that I think will sink the 300 column design, and that is the 8,060 byte limit on a SQL Server row size. A row cannot exceed that size, so you cannot store more than 26 bytes or so for each of the 300 columns without risking the row being rejected. For that reason alone you are better off using the normalized "vertical" design you have now. Additionally, you can index the attribute column more effectively than trying to index 300 separate columns, which is again another limit that exceeds SQL Server's capacity of 253 indexes per table.

And I'll tell ya right now, you DO NOT want to insert values into a 300 column table on a regular basis. The SQL for that gets real bad real fast.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources