Re: ID column naming convention

> On Oct 13, 2015, at 18:27, droberts <david.roberts@riverbed.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
theinformation_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
noteit 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general