On Sat, Dec 18, 2004 at 17:19:27 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:
> I'm working on a database that will contain companies (group) and the
> relevant listing (securities-> id) and related periodic information. I want
> the end users of the data to see the relationship between the 2 (comp A,
> security 01, security 02;comp B, security 01, security 02) in the key
> (company,security) as this would for part of the keys in all the other
> related tables (depending if the info is on a company level or security
> level and e.g using the date). By using a normal increment value as unique
> key, I would loose this relation information in key, no?
No. Though from what you are saying here, it doesn't look like you need
that ID at all.
It looks like there should be a company table, a security table and
a company - security table. You haven't said enough about the peridoic
data to suggest how to handle that.
You probably want to use ID columns for the companies and securities,
because there might be companies with the same name (and perhaps securities
as well).
To model this relation you could use the tables below. (In reality you
probably need something more complicated to handle other information.)
create table company (
id serial primary key,
name text not null
);
create table security (
id serial primary key,
name text not null
);
create table compsec (
company int references company(id),
security int references security(id),
primary key (company, security)
);