> On Oct 13, 2015, at 18:27, droberts <david.roberts@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
