Re: [GENERAL] ID column naming convention

On 10/22/15 11:37 AM, Karsten Hilbert wrote: On Mon, Oct 19, 2015 at 04:25:39AM -0500, Jim Nasby wrote: BTW, if there's any interest, I have code that sets up a non-inheritance table specifically for doing foreign keys to the inheritance parent. It doesn't support updates right now, but should

Re: [GENERAL] ID column naming convention

On Mon, Oct 19, 2015 at 04:25:39AM -0500, Jim Nasby wrote: > BTW, if there's any interest, I have code that sets up a non-inheritance > table specifically for doing foreign keys to the inheritance parent. It > doesn't support updates right now, but should be fully safe from a FK > standpoint.

Re: [GENERAL] ID column naming convention

On 10/18/15 4:32 AM, Karsten Hilbert wrote: Can you tell us more about the database (ignoring nit-picking!)? Sure. All the clinical tables inherit from clin.clin_root_item which itself has a primary key (pk_item) and which, in turn, inherits from audit.audit_fields, which, again, has a primary

Re: [GENERAL] ID column naming convention

> > Here's a real live schema using (mostly) the above approach: > > > > http://www.gnumed.de/~ncq/gnumed/schema/gnumed_v20/ > > > > in case anyone is interested in taking a look. > > > > Karsten > > Apparently (according to your naming convention) several tables (such as >

Re: [GENERAL] ID column naming convention

On 18/10/15 00:13, Karsten Hilbert wrote: On Fri, Oct 16, 2015 at 04:59:12PM -0500, Jim Nasby wrote: BTW, I found Karsten's idea of using 'pk' for the surrogate key, and fk_table_name interesting. It helps avoid ambiguity from externally generated ID values. That's the point :-) Here's a

Re: [GENERAL] ID column naming convention

> On Oct 13, 2015, at 18:27, droberts 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 >>>

Re: [GENERAL] ID column naming convention

On Fri, Oct 16, 2015 at 04:59:12PM -0500, Jim Nasby wrote: > BTW, I found Karsten's idea of using 'pk' for the surrogate key, and > fk_table_name interesting. It helps avoid ambiguity from externally > generated ID values. That's the point :-) Here's a real live schema using (mostly) the above

Re: [GENERAL] ID column naming convention

On 10/15/15 8:28 PM, Gavin Flower wrote: It would seem to be very dodgy to us a join based on apparently very different semantic implied by 'blah.person_id = foo.invoice_id'!!! :-) Because 2 fields in different tables have the same name, it does not necessarily mean they have the same

Re: [GENERAL] ID column naming convention

On Fri, Oct 16, 2015 at 02:28:25PM +1300, Gavin Flower wrote: > Since 'id' is only used to indicate a PRIMARY KEY, there is less confusion > in joins, and it is clear when something is a foreign key rather than a > PRIMARY KEY. Given that "id" often has meaning outside the database I much prefer

Re: [GENERAL] ID column naming convention

On 10/13/15 2:34 PM, Gavin Flower wrote: 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

Re: [GENERAL] ID column naming convention

On 16/10/15 13:09, Jim Nasby wrote: On 10/13/15 2:34 PM, Gavin Flower wrote: 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

Re: [GENERAL] ID column naming convention

On Tue, Oct 13, 2015 at 03:44:15PM -0700, John R Pierce wrote: > On 10/13/2015 3:27 PM, droberts wrote: > >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

Re: [GENERAL] ID column naming convention

On 10/13/2015 11:36 AM, 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

[GENERAL] ID column naming convention

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

Re: [GENERAL] 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

Re: [GENERAL] ID column naming convention

On Tue, Oct 13, 2015 at 1:36 PM, droberts wrote: > I 'think' if I don't enforce foreign key constraints, then this practice > prevents tools from being able to generate ERD diagrams right? > ​Yes, if you don't name them the same and don't setup an actual FK then

Re: [GENERAL] ID column naming convention

On 10/13/2015 3:27 PM, droberts wrote: 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.

Re: [GENERAL] 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 >> >>