Database Design questions

  • From: Pallav Kalva <pallav_oracle@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 18 Jun 2004 06:49:23 -0700 (PDT)

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
-----------------------------------------------------------------

Other related posts: