RE: Criteria for handoff from development

From: Kimberly Smith <ksmith2_at_myfirstlink.net>
Date: Mon, 07 Jan 2002 07:51:07 -0800
Message-ID: <F001.003E861E.20020107072525@fatcity.com>

Actually, even if the database is going to be synced with another sequence generated numbers is still the best. I worked on a replication project once and what we did is have one database generate even numbers and the other generate odd numbers. I don't remember how we did it exactly and am to lazy to try it but it worked well.

-----Original Message-----

tday6_at_csc.com
Sent: Monday, January 07, 2002 5:50 AM
To: Multiple recipients of list ORACLE-L

I agree with the column naming comments. I would find it hard to over-emphasize the need for a column naming convention that allows you to know what table a column belongs to, whether it's a primary or foreign key, and ,if it's a foreign key, then what the name of the base table and column are. I also agree with the comments on choosing a primary key. If the database is never going to by synched with an outside database then the best choice for a primary key is a sequence generated number.

Personally, I wouldn't worry too much about normalization problems if they're deliberately done. It's easy to gong developers over normalization, but sometimes a denormalized table is necessary for performance. In fact, if the data model is perfect 3rd normal form then you'll probably have to do some denormalization.

All that being said, it's silly to have the developers present you with a data model. You should have been involved in developing the data model from the very beginning. But life, or management, is always presenting us with new "opportunities." Good luck.

                    "Mercadante,
                    Thomas F"            To:     Multiple recipients of list
ORACLE-L
                    <NDATFM              <ORACLE-L_at_fatcity.com>
                    @labor.state.        cc:
                    ny.us>               Subject:     RE: Criteria for
handoff from
                    Sent by: root        development


                    01/04/2002
                    03:50 PM
                    Please
                    respond to
                    ORACLE-L

Dennis,

First of all, I would tell your manager that 90% of tuning is in writing good queries no matter what the data model looks like.

Unfortunately, you receiving a data model and expecting to perform miracles is pretty naive of the organization. This is a classic example of how NOT to do things.

Saying that, I would look closely at the model and check for the following:

Look closely for normalization problems. If you see repeating fields in a table, reject it and tell them to change it.

Look for column-naming standards. If they do not have them, make some up and enforce them. Some common naming standards would use a suffix to indicate the type of data the column is holding. Things like _DATE _NBR _FNAME _LNAME _ID and _CODE would indicate date, number, standard length first and last name, Id type columns indicating it is a primary key (possibly) an integer value, and a Code column indicating that this is a foreign key to another table. This is soooo important for report-writing people on the back-end of the project. They can implicitly see that the column has a certain value by the name.

Ask how they determined primary key values for all tables. Specifically, how do they KNOW that the values will be unique. Question everything you see. This is probably the biggest area of concern that I would have. Non-db designers will always make a mistake here. I developed a db once that used the soc-sec as the pk. WRONG! The db was at a college. Want to know how many parents use their personal soc-sec on the application for the child? :(

Look for obvious foreign key relationships and enforce them. Develop a standard where the related columns in database tables (like FK columns) have
the same name in both tables (like soc_sec_number is named the same in all tables).

This is not an easy thing to do. You should have been involved in the meetings from the very get-go. Hopefully, this is not a 300-table design that will take you very long to review.

Hope these help!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Friday, January 04, 2002 2:45 PM
To: Multiple recipients of list ORACLE-L

Can anyone provide some criteria of what you look for when a data model is handed off from production? We are starting a large development project and I lobbied management to hire a data architect. As they have talked to these people, they are getting statements such as "and then the DBA will check out
the data model to make sure there won't be any performance problems". I am concerned about what will be expected of me and wondered how other DBAs handle this situation. What do you look for in a model in terms of making sure the performance will be good? I said that I could look at the queries that would be run to see how many tables would need to be joined to retrieve
the data, but the manager replied that a good DBA wouldn't need to see the queries, should just be able to look at the model. Up until this point, our client-server design tools have tended to protect the developers from doing dumb stuff, but now in the Java world some of those safeguards.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: tday6_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kimberly Smith
INET: ksmith2_at_myfirstlink.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message