Re: ID column naming convention

Gavin Flower-2 wrote
> 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
>
>
> --
> Sent via pgsql-general mailing list (

> pgsql-general@

> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Thanks. My only question is how do you create a schema diagram (ERD) then?
The tool won't know what the relationships are unless maybe you put foreign
key constraints on. BTW does anyone recommend a tool to to that? I've been
playing with DbVisualizer.

--
View this message in context: http://postgresql.nabble.com/ID-column-naming-convention-tp5869844p5869881.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.