Basic Question on Join

[nick]

Hi,

I'm pretty much a database beginner and have what I think is a basic
question:

If I have a table which has a 'status' column and I can have say three
statuses: "active", "pending", "inactive". Is it better to break these
out into a different "Statuses" table?

On the one hand it seems to me it would be better to be in a different
table since I'll index this column because I'll be in a where clause
sometimes and I think indexing it would be easier than indexing the
actual words. On the other hand, are joins going to slow queries down
too much? I read something about creating my own sql server 2000
datatype - would this be a good place to do such a thing?

Thanks for your help. (And if you could provide a reason why one
solution is better than the other so I can start understanding better,
I'd be very appreciative.)

Thanks.

[nib]

Re: Basic Question on Join

nick wrote:[color=blue]
> Hi,
>
> I'm pretty much a database beginner and have what I think is a basic
> question:
>
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
>
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
>
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)
>
> Thanks.[/color]

If you're storing addresses should you have 50 separate tables, one for
each state?

Zach

[Terri]

Re: Basic Question on Join


"nick" <nickgieschen@h otmail.com> wrote in message
news:305cab98.0 411191325.29e48 efc@posting.goo gle.com...[color=blue]
> Hi,
>
> I'm pretty much a database beginner and have what I think is a basic
> question:
>
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
>
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
>
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)[/color]

Having a seperate lookup table may give you a better performance, testing is
the best way to determine. The lookup table could have an numeric value
that corresponds to active, pending, inactive and then you store the numeric
value in your main table. You wouldn't necessarily have to join to the
lookup table, you could just say WHERE Status = 1.

Using lookup tables can be more flexible for front-end coding because if
you have additions to your lookup choices you can simply edit the table
rather than digging around in the front-end code.

I wouldn't think a user-defined datatype would be useful for this.

hth

[Erland Sommarskog]

Re: Basic Question on Join

nick (nickgieschen@h otmail.com) writes:[color=blue]
> I'm pretty much a database beginner and have what I think is a basic
> question:
>
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
>
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?[/color]

Basic question? It is actually one that I run into when I'm database design
every now and when, and the answer is not always the same. Mainly it is a
judgement's call. But these days, I tend to go for the table.

I like to first point that when I decide whether to add a table, or to
just have a column with some distinct values, performance has none to with
that decision. More important is the aspect of maintenance, the possibility
to present the value, extensibility and whether the value is important
enough to deserve a table.

Having a single column like:

status chat(1) NOT NULL CHECK (status IN ('A', 'P', 'I'))

has the advantage that it's up little space in the documentation, and
so easier to grasp. But if the value is to be presented in a GUI, the
GUI gets responsible for doing the translation (or the user gets to see
A, P and I. Another issue, is whether you need to add a new value. For
various reasons when we change tables, our standard routine is to take
the long way and reload the data into a new table. A bit expensive for
a changed CHECK constraint.

If you have a column with a lookup-table like:

status char(1) NOT NULL REFERENCES statusvalues (status)

this is more work for me initially, because I have to create an INSERT-
file with the statusvalues, and there is one more table in the database.
(In our case actually two, because there would also be a name table
that gives translations in different languages.). But if I later need
to add a new value, it's just a matter of changing that INSERT-file and
no need to reload the table. And with all strings in the database, the
GUI gets a simpler job.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp