one or many tables - SQL Server Forums

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)

one or many tables

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2002-11-07 : 14:16:36

Hi,
I'm developing a database that will store lots of items for one record. There is always a one to one relationship for all of these items. I am curious if it is better to have one table with many columns (maybe 60) or break it down into a number of tables each containing 5 or so columns. Some of the items could be grouped together (are somewhat related) so splitting them into a seperate table could make sense, but many of the items really have no correlation to any other items. Obviously, if there is a many to one relationship you would need multiple tables but I was curious if there is any difference between having multiple tables (with one record) or one table (with one record).

Thanks

Nic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-07 : 14:30:10

I'd have to see more specifics, but I think multi tables are generally a better way to go.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-07 : 14:37:36

quote:
I'd have to see more specifics, but I think multi tables are generally a better way to go.

Really? So how many tables would you make out of Northwind.dbo.Products?

EDIT: W.W.C.D*?

*(What Would Codd Do)
Jay White
{0}

Edited by - Page47 on 11/07/2002 14:45:21

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-07 : 15:21:19

Ummm.. Codd would kinda flap/flounder around on land till ya put the fish back in water?

Actually, more serious note... What do you guys think of name/value pairs?

IDcol,col_name,value

For some reason the third party software used here uses this in all its tables. It's really hard to follow everything, but they have a 3 column table hold data that would normally need about 40 columns to hold. Any comments on this? good/bad? appropriate for nic here?

-----------------------
SQL isn't just a hobby, It's an addiction

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-07 : 15:25:45

I think i misunderstood the original question. I was thinking more like this:
A product has N colors
A product has N sizes
etc.

Nic, one solution might be a key/value type setup. You can also do stuff like "default values" etc for the different params etc.


CREATE TABLE #Item(ItemID INT, ItemName VARCHAR(50))
CREATE TABLE #Param(ItemID INT, ParamName VARCHAR(50), ParamValue VARCHAR(100))

INSERT INTO #Item(ItemID, ItemName) VALUES(1, 'Square Widget')
INSERT INTO #Item(ItemID, ItemName) VALUES(2, 'Round Widget')
INSERT INTO #Item(ItemID, ItemName) VALUES(3, 'Triangle Widget')

INSERT INTO #Param(ItemID, ParamName, ParamValue) VALUES(1, 'Sides', '4')
INSERT INTO #Param(ItemID, ParamName, ParamValue) VALUES(1, 'Color', 'Red')

INSERT INTO #Param(ItemID, ParamName, ParamValue) VALUES(2, 'Sides', '0')
INSERT INTO #Param(ItemID, ParamName, ParamValue) VALUES(1, 'Color', 'Blue')

INSERT INTO #Param(ItemID, ParamName, ParamValue) VALUES(3, 'Sides', '3')

SELECT i.ItemID, i.ItemName, p.ParamName, p.ParamValue
FROM #Item i
	INNER JOIN #Param p ON p.ItemID = i.ItemID
ORDER BY i.ItemID

DROP TABLE #Param
DROP TABLE #Item

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-11 : 13:56:37

The two obvious problems with this {name/value pair, key/value type setup} method described are a.) datatype of value and b.) integrity constraints. So would you type all the values as varchars? And how do you enforce only colors in table Colors?

You know, I'm not sure what Codd would do ... and that distresses me a bit. C.J. Date seems to think this issue requires the creation of a new design principle: The Principle of Orthogonal Design Part 1 Part2. That seems to be what my gut says, too ... don't create two tables with overlapping meanings.

M.E. and MichaelP: care to detail why your solution is justified? Anyone else have a comment here? Unless I'm convinced otherwise, if you go through the normalization process and end up with a table with 200 columns, so be it. Codd's first rule tells us to represent all information as values in tables (and nothing else). Changing the meaning of one column based on the value in some other column seems to violate this, as it introduces some sort of implied dependancy of meaning. It just doesn't feel right.

EDIT: ...
Couldn't you take any 3NF design and rebuild the entire schema with just 1 table...


create table database (
    entity varchar(),
    key varchar(),
    property varchar(),
    value varchar(),
    constraint pk_database primary key (entity,key,property)
 )

Right? Couldn't you do that? Absurd, yes, but is it any different than the original question?

Jay White
{0}

Edited by - Page47 on 11/11/2002 14:10:55

Edited by - Page47 on 11/11/2002 14:14:41

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-11 : 15:17:48

Careful, Jay -- lose another column and you'll end up with RDF statements there! From there it's just a matter of time before you fall prey to the ontology monster!

r937
Posting Yak Master

112 Posts

Posted - 2002-11-11 : 15:21:09

quote:
Changing the meaning of one column based on the value in some other column seems to violate this, as it introduces some sort of implied dependancy of meaning. It just doesn't feel right.

what if you have a numeric column called Weight and another one right beside it called UnitOfMeasure

then you can have a row with 80 kg, and another with 165 lb, and so on

i've never found a better way to model that

it is an intra-column relationship, but it makes me only mildly uncomfortable

rudy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources