Re: designing tables - sidetrack into arrays

On Wednesday, July 30, 2003, at 12:14  AM, Cath Lawrence wrote:

> OK, a followup to my question now before anyone replies...
>
> On Wednesday, July 30, 2003, at 04:01  PM, Cath Lawrence wrote:
>> Perhaps I should make a special archive table and refer to it by some
>> key. Say, id, [array of archive content file names], bytea for the
>> archive.
>
> Actually this now strikes me as a bad idea, since I think I can't
> search my table by filename to find what archive it's in, and retrieve
> that archive. With a plaintext list I could do
> "select archive from bin_archives where file_name_list like
> ('%my_wanted_file%')"
>
> Or am I being clueless about arrays, too?

This sounds like you want to have a one-to-many relationship between
your record and its associated files. What you need to do is create a
separate table that will reference your record id:

CREATE TABLE associated_files (
   fileid    serial  PRIMARY KEY,
   recordid  int     REFERENCES master_records (id),
   filepath  text    NOT NULL
);

Does that help?

--Andrew Vit