[thelist] DB Schema Design

[Matt Warden]

Ian,

On 5/8/06, Ian Anderson <ian at zstudio.co.uk> wrote:
> >> This table is not normalised: the values for class should be moved out
> >> into some other table and related with a foreign key.
> >
> > You can do this, but it is not necessary.
>
> Mmmm. No, not necessary, but good practice I'd say.
>
> Consider any other single column with a relatively fixed set of values.
> You could argue the same, mais non?

There's really not much to be gained from moving this out to another
table, except a little bit of disk space. There's no reason here to
have a status type (here called a 'class') with no statuses, which
would be the other reason to move it out into another table.

status
----------------------------
1        new           product
2        shipped       product
3        canceled      product
4        active        user

vs.

status
-----------------------------
1        new           1
2        shipped       1
3        canceled      1
4        active        2

> >> And the values in this table would then need to define which table in
> >> the database the associated statuses belong to:
> >
> > Why? As far as I can tell, there's no reason to do this.
>
> It's called "Being hit by a bus".
>
> If the developer ain't around no more for any reason, I'd like there to
> be an explicit notation of which table these puppies belong to, and not
> just in a ring binder on some shelf with three thousand other pieces of
> paper, neither.

I see. I thought you were discussing referential integrity, which is
why I was confused.

However, it would be very clear what table a given row "belongs to":

select s.* from status s
inner join product p on p.statusid=s.statusid

or

select * from status
where class='product'

Thanks,

--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.