Skip site navigation (1) Skip section navigation (2)

Re: designing tables - sidetrack into arrays

From: Andrew Vit <andrew(at)dihedral(dot)ca>
To: Cath Lawrence <Cath(dot)Lawrence(at)anu(dot)edu(dot)au>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: designing tables - sidetrack into arrays
Date: 2003-07-30 20:01:38
Message-ID: A1378D0A-C2C8-11D7-8704-000502742A2D@dihedral.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: Lynna LandstreetDate: 2003-07-30 23:03:18
Subject: pg_atoi error?
Previous:From: MiernikDate: 2003-07-30 17:59:07
Subject: Re: ALTER TABLE DROP COLUMN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group