Toad World® Forums

Best Practices for PK/FK naming and autogeneration


#1

I am new to TDM3 but have been doing data modeling for many years (with Oracle Designer 10+ yrs.)

I am now working at a place that is using MySQL and may move to Oracle some day. So I said “Let’s use TOAD!”.

My problem: We will be using Java and Hibernate to connect to the database. The Java/Hibernate expert says the standard he is expecting is that the PK for every lookup table will be called ID and when it appears as FK in a child table it should be named _ID. This means a) the FK and the PK that it is based on are not named identically to each other b) WHERE clauses are written like this: where tablename_id = tablename.id and of course this means c) auto transfer of PK to child table in modeling tool is not so automatic any more.

Has anyone else ever seen this Java standard? Can I trick TDM3 into doing this without manually creating and reattaching every FK to a new column?

Is there a generation option that makes this possible?

Please help.


#2

Hello,

Thanks very much for raising this issue. Firstly, we need to discuss this issue in our team. If I have more questions or as soon as I have some news for you, I’ll write.
Thanks for your patience!

Nevertheless, at the moment, we can offer you only a script that would rename the PK and FK attributes at one jump.
PK attributes would be called ID and FK attributes would be named _ID.
Just one question - what if you have more attributes in a PK. How do you name them? Entity can’t have two attributes of the same name ID…

Please write us if you are interested to have the script + some details so as the script would meet your requirements.
Thanks in advance.

Looking forward to your reply.

Regards,

Vladka + TDM Team


#3

Thanks for your prompt response.

regarding your question:

what if you have more attributes in a PK. How do you name them?

You are absolutely right, a “concatenated primary key” (or compoundkey) made of several columns would then have to be turned into a unique ID onlymade up of the IDs of each parent table. The PK would just be theID. An example I have now is as follows:

CREATE TABLE SCHOOL_DEGREE_PROGRAM
(
SCHOOL_ID Bigint NOT NULL,
AREA_OF_STUDY_ID Bigint NOT NULL,
SPECIALIZATION_ID Bigint NOT NULL,
DEGREE_TYPE_ID Bigint NOT NULL,
VERSION Bigint NOT NULL,
SHORT_DESCRIPTION Char(12) NOT NULL,
LONG_DESCRIPTION Char(80) NOT NULL,
CREATE_DATE Timestamp NOT NULL,
UPDATE_DATE Timestamp
);

ALTER TABLE SCHOOL_DEGREE_PROGRAM ADD PRIMARY KEY(SCHOOL_ID,AREA_OF_STUDY_ID,SPECIALIZATION_ID,DEGREE_TYPE_ID);

Using this revised approach it would have to be something like this:

CREATE TABLE SCHOOL_DEGREE_PROGRAM
(
ID Bigint NOT NULL,
SCHOOL_ID Bigint NOT NULL,
AREA_OF_STUDY_ID Bigint NOT NULL,
SPECIALIZATION_ID Bigint NOT NULL,
DEGREE_TYPE_ID Bigint NOT NULL,
VERSION Bigint NOT NULL,
SHORT_DESCRIPTION Char(12) NOT NULL,
LONG_DESCRIPTION Char(80) NOT NULL,
CREATE_DATE Timestamp NOT NULL,
UPDATE_DATE Timestamp
);
ALTER TABLE SCHOOL_DEGREE_PROGRAM ADD PRIMARY KEY (ID);

ALTER TABLE SCHOOL_DEGREE_PROGRAM ADD UNIQUE(SCHOOL_ID,AREA_OF_STUDY_ID,SPECIALIZATION_ID,DEGREE_TYPE_ID);

At the very least this means that you would be creating two indexes, one for PKand one to ensure unique combinations. My programmer promptly notices HOWMANY JOINS he would have to make to access this data! (well, yes…)

I think this very quickly gets us into the debate about “How do you createa normalized database design that will be accessed by OO programminglanguages.” What they want is a single OID (object ID) that they canuse to access one “object” in the database (actually row in a table).

Bob


#4

Hi Bob,

Thanks very much for the details.

What we are able to do for you at the moment is this:
We can write a sample script that you can then modify to fit your needs. The script will go through all attributes in keys and will do this:

  1. If an attribute is PK and is the only attribute in the PK, it will be renamed to ID.
  2. If the script finds a compound PK, it will change it to alternate key and a new PK with one attribute ID will be created.
  3. If the script finds a compound FK, the same will be done as in #2 above. -> This will also cause a change of relationship type from identifying to non-identifying.

Please let us know if it is acceptable for you. Thanks.

What we are NOT able to do at the moment is to apply the change of the names in SQL code of procedures, triggers etc. We’re sorry.

We look forward to hearing from you.

Regards,

Vladka + Daril


#5

I now have the same problem. Were there any changes made to 3.4 to fix it?

If the script is available I’d love to get a copy.


#6

Hello,

The script finally wasn’t written. We didn’t get any response from Bob.
However, please read our last post with the script description (of Jun 4, 2008) and let us know if the script meets your needs.
Thanks for your confirmation.

Regards,

Vladka + TDM Team


#7

To be honest I don’t like such techniques (suggested by Java/hibernate technologies).
This technique has been created only to simplify recognition of PK by column name.

But I believe, it is wrong from modeling point of view it self.

In many cases, in complex queries, it will result a lot of ID fields mixed, which must be prefixed by table names. It will be not easy readable by developer.
More, even in case of simply join of 2 tables, JOIN USING syntax will be not possible.

best regards