SEO

Hi SQL gurus,


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 :


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


Costs
-------------
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name


or is it better to create one single table that will contain both
entities like that :


Incomes_Costs
-------------
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome


which only differs from the 2 above by the isIncome field to know which

row is an income and which row is a cost.


I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.


Thanks all for your time!


ibiza

By no means a "Guru"...but If I'm following you...

This is the age old question of "to normalize" or not to "normalize".

By splitting the tables you achieve a higher level of normalization.
This will cause queries etc to run somewhat quicker..but you wll take a
hit in terms of storage space.

By making one table...you will speed up your queries...but waste space.

HTH

MJKulangara
http://sqladventures.blogspot.com

Ok, I am really speeding up my queries (by 1/1000ths of second -_-)with
two table? I thought it would be the inverse...how come?

And about the storage, why would it take more space? I mean, if I have
4 incomes and 4 costs splitted into two tables, it's about the same
amout of data as 4+4 rows in the same table (I suppose the only
difference is one bit for each row for the 1 table layout VS some tiny
space used for the definition of one more table in the 2 tables
layout?)

The point here is I want to get to know these little subtleties...

thanks for your advice!

ibiza

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

hmmm. i'd think you would be happier with even more abstraction then
either option.

have one table for models. it has the modelid, and the modelname, and
one row for each model.
then have one table for "dollars."
this will have "dollarid", a flag for cost or income, and a human
name. there will be one row for each type of income or cost, so the
total rows will be something like 14.

then have the "xref" table. It will have modelid, dollarid, and
dollaramt.
this will have one row for each model for each cost/income.

the advantage is that over time new costs or incomes will show up, and
you can easily add them. further, you don't have to have really awful
program logic to add up all of your costs and income for a specfiic
model.
hope this helps,
doug