Posted by Erland Sommarskog on 02/03/06 00:16

ibiza (lambertb@gmail.com) writes:
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :

That is not really possible to answer without further knowledge about
the business domain. And, even I would have it, I would maybe still be
a trade-off for me which way to do it.

A key here is how related they are. If they mirror each other, and
are two sides of the same coin, it may make sense to have them in same
table. If they are unrelated, they should not be in the same table.

Another observation:

> Incomes
> -------------
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name

Maybe it makes sense to have seven columns, but a more conventional
design would be to have a main table:

CREATE TABLE incomes (in_idmodel int NOT NULL,
in_name varchar(30) NOT NULL,
CONSTRAINT pk PRIMARY KEY(in_idmodel))

CREATE TABLE incomerows (in_idmodel int NOT NULL,
rowno smallint NOT NULL,
value smallmoney NOT NULL,
CONSTRAINT pk2 PRIMARY KEY (id_idmodel, rowno))

An important advantage is that this design is not tied to fixed number
of levels.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]