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.
