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.
