Re: Layout question

On 4/6/06 8:06 AM, "Jan Danielsson" <jan(dot)danielsson(at)gmail(dot)com> wrote:

> Hello all,
>
> I need some advice on how to design some tables. I'm new at this SQL
> stuff. I lack the experience to know how a decision will affect me down
> the road..
>
> I have a table containing "files", which is appropriately called
> 'files'. But files represented in this table have two subcategories:
> text files and binary files.
>
> The text files have certain properties which the binary don't and
> vice versa. For instance, the text files have "encoding", while the
> binary have "compression".
>
> I have this:
>
> ------------------------
> create table files (
> id serial primary key unique,
> added timestamp default current_timestamp
> )without oids;
>
> create table textfiles (
> file_id references files(id) match full on delete cascade not null,
> encoding_id references encodings(id) match full on delete cascade not null
> )without oids;
>
> create table binaryfiles (
> file_id integer references files(id) match full on delete cascade not null,
> compress bool default 'true' not null
> )without oids;
> ------------------------
>
> To get a list of files, I would obviously have to perform a select
> with two joins, which would cause either encoding_id to be null, or
> compress.
>
> The reason I have two separate tables is because I don't like the
> idea of each file entry wasting space in the database for attributes it
> doesn't have. But if I didn't care about that, I could create a table:
>
> ------------------------
> create table files (
> id serial primary key unique,
> added timestamp default current_timestamp
> encoding_id references encodings(id) match full on delete cascade
> compress bool default 'true'
> )without oids;
> ------------------------

I would go with the latter, myself just because it is simpler, but I don't
think there is a right answer. Storing NULLs in tables is fine.

If you really need the behavior of two tables, you might consider looking at
inheritance.

Sean