Обсуждение: Layout question

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;
------------------------

   Some of you may think "well, maybe you want to compress a text file",
but no. Believe me, they are mutually exclusive. Always. No exceptions.

   Is there any reason to chose one method over the other? I'm inclined
to go with the first solution, but it does complicate queries quite a
lot. I'm not really looking for a "Go with method x"; but rather looking
for pros and cons.

--
Kind Regards,
Jan Danielsson
Te audire non possum. Musa sapientum fixa est in aure.

On 4/6/06 8:06 AM, "Jan Danielsson" <jan.danielsson@gmail.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

I have something similar to your setup, and I use what Jan suggested.  In my case, the tables "textfiles" and "binaryfiles" inherit from "files".  My table "files" (and therefore the two children) contains a column "kind" that differentiates between the two children:  it's a one-character field, constrained to be equal to either 't' or 'b'. In the children tables, I addded to that column a default value: 't' in one case and 'b' in the other.

It's not perfectly satisfactory, but it's the best I have found so far..