Re: ID column naming convention

> On Oct 13, 2015, at 18:27, droberts <david(dot)roberts(at)riverbed(dot)com> wrote:
>
> 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.
That's how most tools work, usually by calling the driver api (jdbc databasemetadata, etc....) which in turn look at the information_schema. If you don't setup real referential integrity, any tool that can use names is just guessing

I think dbvisualizer will 'infer' based on column names. I KNOW that schemaspy has this option, but they explicitly note it is a GUESS.

Use foreign keys.

> 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general