Re: ID column naming convention

On Tue, Oct 13, 2015 at 1:36 PM, droberts <david(dot)roberts(at)riverbed(dot)com>
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
there is no reason to assume that two random fields on separate tables are
in any way related.

I suspect Rob is right that even naming them the same isn't enough - but
that would depend upon the tool. Given the, IMO misguided, prevalence of
naming every PK fields "id" about the only meaningful logic such a tool
could employ would be to look for fields of the form "table_id" and if
"table" has an "id" field assume that they are indeed related. Since your
example use "my_dim" for a field that exists on the "mydimtable" even this
logic would be insufficient to guess the link you know exists but don't
encode into the schema.

Personally, I don't care whether it is a PK or FK - ID fields are database
unique and when present always refer to the same entity. In this case I
would call the field "mydim_id" on both tables. I would expect to have to
define UNIQUE (PRIMARY KEY) and FOREIGN KEY constraints if I wanted tools
to understand the relationship between the two tables even though I
standardized the name of the ID field.​

There are implications to choosing any particular naming convention. I'm
not familiar with any that are so severe that I would call them problems.

David J.