Re: ID column naming convention

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