RE: Database Design questions

  • From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 18 Jun 2004 09:59:40 -0400

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@xxxxxxxxx] 
Sent: Friday, June 18, 2004 9:49 AM
To: oracle-l@xxxxxxxxxxxxx
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.

1) Is this a good design for such a big table size ?
2) 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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: