RE: Database Design questions

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 18 Jun 2004 09:59:40 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5C44@exchsen0a1ma>

Pallav,

Generally, the number of columns that a table has does not effect performance.
In your example, do any of the elements have multiple copies of things stored in the one table? - for example, it seems like "property_loans" would possibly have more than one set of data. I can see a piece of property having several loans. In this case, if would be a child table and would require a new table. Same things for auctions and defendants.

If you can make a case for creating a child table, then go ahead. But creating one-to-one relationships between tables does not make sense to me.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

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

From: Pallav Kalva [mailto:pallav_oracle_at_yahoo.com] Sent: Friday, June 18, 2004 9:49 AM
To: oracle-l_at_freelists.org
Subject: Database Design questions

Hi Design Experts,

I am in the process of designing a datamodel for a new application and I need some advice regarding that. I have a table with almost 130 columns in it. This table gets updated with new information regularly and all the columns are mostly dependent or related to the primary key. I think there will be a poor performance with the table of this size. My question is it better of breaking this table into several different tables like

property_details ( prop_id (pk), prop address, bed rooms,bath rooms ,total sqft ...)
property_auctions ( prop_id (fk), auction date, auction location..)
property_loans, (prop_id (fk), loan date, loan amount, loan recording date...)
property_defendents (prop_id (fk), defendent owner firstname, lastname, company name ...)

.
.
.

Each of these tables will have prop_id as a Foreign Key (which is also a primary key for that table ) referencing the prop_id Primary Key in this case parent table property_details.

Is this a good design for such a big table size ?
If I choose this approach and when i need to get all the information for a property I will end up querying all the tables and will involve joining all the tables, which would also affect the performance of the query.

Can anyone help please help me with the problem ? Below you will see the huge table which I am talking about.

Thanks!
Pallav.

Table PropTaxroll

Prop_ID
State
County
Batch Date & Seq Number
Deed Category
Document Type
Recording Date
Document Year
Document Number
Document Book
Document Page
Title Company Code
Title Company Name
Attorney Name
Attorney Phone Number

1st Defendant/Borrower/Owner First Name
1st Defendant/Borrower/Owner Last Name
1st Defendant/Borrower/Owner Company Name
2nd Defendant/Borrower/Owner First Name
2nd Defendant/Borrower/Owner Last Name
2nd Defendant/Borrower/Owner Company Name
3rd Defendant/Borrower/Owner First Name
3rd Defendant/Borrower/Owner Last Name
3rd Defendant/Borrower/Owner Company Name
4th Defendant/Borrower/Owner First Name
4th Defendant/Borrower/Owner Last Name
4th Defendant/Borrower/Owner Company Name Defendant/Borrower/Owner Et Al

Indicator Filler1 Date of Default Amount of Default Filler2 Filing Date Court Case Number Lis Pendens Type Plaintiff 1 Plaintiff 2 Final Judgment Amount Filler3 Auction Date Auction Time Street Address of Auction Call City of Auction Call State of Auction Call Opening Bid Filler4 Tax Year Sales Price Situs Address Indicator1 Situs House Number Prefix1 Situs House Number1 Situs House Number Suffix1 Situs Street Name1 Situs Mode1 Situs Direction1 Situs Quadrant1 Apartment Unit Property City1 Property State1 Property Address Zip Code1 Carrier Code Full Site Address (Unparsed)1

Lender/Beneficiary First Name Lender/Beneficiary Last Name
Lender/Beneficiary Company Name Lender/Beneficiary Mailing Address
Lender/Beneficiary City Lender/Beneficiary State Lender/Beneficiary Zip

Lender Phone Filler5 Trustee Name Trustee Mailing Address Trustee City Trustee State Trustee Zip Trustee Phone Trustee's Sale Number Filler6 Original Loan Date Original Loan Recording Date Original Loan Amount Original Document Number Original Recording Book Original Recording Page Filler7 Parcel Number (Parcel ID) Parcel Number (Unmatched ID) Last Full Sale Transfer Date Transfer Value Situs Address Indicator2 Situs House Number Prefix2 Situs House Number2 Situs House Number Suffix2 Situs Street Name2 Situs Mode2 Situs Direction2 Situs Quadrant2 Apartment Unit2 Property City2 Property State2 Property Address Zip Code2 Carrier Code2 Full Site Address (Unparsed)2 Property Indicator Use Code Number of Units Living Area Square Feet Number of Bedrooms Number of Bathrooms Number of Garages Zoning Code Lot Size Year Built Current Land Value Current Improvement Value Filler8 Section Township Range Lot Block Tract/Subdivision Name Map Book Map Page Unit # Expanded Legal Legal 2 Legal 3 Legal 4

Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

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

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


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

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Jun 18 2004 - 08:56:46 CDT