Performance and data structure question

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
Ibiza,
for a while no response to your post, I'll post mine. I would not claim me
being an SQL guru (the reason people shied away from responding?), though I
consider myself a seasoned SQL Server professional.
To me there is no doubt that they should be two separate tables. Even
though they look structurally alike, logically they are different things.
They may be used together in some purpose (like calculating net profit), but
one is the cost and the other one is the income.
One thing made them seemingly alike is your way of naming the columns, which
I think is not so optimal and introduced the illusion. Instead of in_1,
in_2, c_1, c_2 etc., I would rather name them TV_ad, Newspaper_ad, etc.,
that way you know from the name of the columns what for income or expense is
being referred to, and you would not have the illusion that they might be
the same thing. Beyond, after the 7 or so items, when in the future you
realize there are other cost that not have an income counterpart (we
actually can not talk about counterpart, but just let's say so), you would
benefit from the separated tables.
Further beyond, I am questioning whether those two tables are the right
approach. To me I should have a income table and a cost table, with the
columns id, source (or expense_item for cost), amount. your id would map to
the Model table so you would not have the name in the cost or income tables.
You may want to have check tables for the source or expense_item as
constraint for input to the income or cost table. Whether to use these
check tables is a question of denormalization, you may choose not to use it,
just like you have chosen to denormalize your Models and Income/Cost tables.
Quentin
"ibiza" <lambertb@.gmail.com> wrote in message
news:1138898200.231048.286540@.g47g2000cwa.googlegr oups.com...
> 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
>
|||Thank you very much for your advice Quentin.
As for my simplistic naming of rows with only numbers, it's because an
income/cost consists of 7 values, each one corresponding to a year. The
middle column(in_4) corresponds to the value of the actual year, the 3
columns before correspond each to years before the actual year and the
3 columns after correspond each to years before the actual year. For
example, if the model was created in 2006, then in_1 would represent
income for year 2003, in_2 : 2004, in_3 : 2005, in_4 : 2006, in_5 :
2007, in_6 : 2008 and in_7 : 2009. As a models can be created for any
year, I cannot really associate each column of income/cost to a
name...I hope I'm not too foggy here and you still understand my logic.
Am I still doing it the correct way?
Thanks a lot!
ibiza