Re: inheritance - Mailing list pgsql-general

Impatient? That was six seconds from first to second post.

So the gist of what you are getting is that you'd like to have a many/many
relationship between your urls and other stuff? This isn't a hard thing at
all and inheritance has nothing to do with it. Just create a table
structure like the article says and you use joins to get the data back
together.

CREATE TABLE users (
    user_id SERIAL
        PRIMARY KEY,
    username VARCHAR(32)
        NOT NULL
        UNIQUE
        CHECK (username != '')
);

CREATE TABLE urls (
    url_id SERIAL
        PRIMARY KEY,
    url TEXT
        NOT NULL
        UNIQUE
        CHECK (url != '')
);

And what you asked for:

CREATE TABLE rel_users_urls (
    r_u_b_id SERIAL
        PRIMARY KEY,
    user_id INTEGER
        NOT NULL
        REFERENCES users
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    url_id INTEGER
        NOT NULL
        REFERENCES urls
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    UNIQUE (user_id, url_id)
);

SELECT
us.username,
ur.url
FROM users AS us, urls AS ur, rel_users_urls AS uur
WHERE us.user_id = uur.user_id AND uur.url_id = ur.url_id
ORDER BY us.username, ur.url;

Joshua b. Jore ; http://www.greentechnologist.org ; 1121 1233 1311 200
1201 1302 1211 200 1201 1303 200 1300 1233 1313 1211 1302 1212 1311 1230
200 1201 1303 200 1321 1233 1311 1302 200 1211 1232 1211 1231 1321 200
1310 1220 1221 1232 1223 1303 200 1321 1233 1311 200 1201 1302 1211 232
200 1112 1233 1310 1211 200 1013 1302 1211 1211 1232 201 22

On Wed, 5 Jun 2002, ktt wrote:

> hello,
>
> I would like to collect indexes of a few other tables
> into one table in order to access their values more
> easier.
> like in "urls" table here:
>
> http://www.phpbuilder.com/columns/barry20000731.php3?page=4
>
> But inserting the same values into many tables isn't
> very convienent.
> Shoul I use inherited tables, aliases, joins?
> That metho is the most effective here?
> PHP will be used to manipulate the data.
>
> thank you in advance,
>
> ktt
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>