Re: ID column naming convention - Mailing list pgsql-general

On 14/10/15 06:36, droberts wrote:
> Hi, is there a problem calling ID's different when used as a FK vs table ID?
> For example
>
>
> mydimtable ()
>   ID
>   name
>   description
>
>
> myfacttable ()
>    my_dim_id   # FK to ID above
>    total_sales
>
>
> I 'think' if I don't enforce foreign key constraints, then this practice
> prevents tools from being able to generate ERD diagrams right?
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/ID-column-naming-convention-tp5869844.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
My practice is to name the PRIMARY KEY as id, and foreign keys with the
original table name plus the sufiix_id.

By leaving the table name off the primary key name, and just using id,
makes it more obvious that it is a primary key (plus it seems redundant
to prefix the primary key name with its own table name!).

CREATE TABLE house
(
     id      int PRIMARY KEY,
     address text
);

CREATE TABLE room
(
     id       int PRIMARY KEY,
     house_id int REFERENCES house(id),
     name     text
);


There are exceptions like:

CREATE TABLE human
(
     id        int PRIMARY KEY,
     mother_id int REFERENCES human (id),
     father_id int REFERENCES human (id),
     name      text
);

Cheers,
Gavin