Database Design questions

From: Pallav Kalva <pallav_oracle_at_yahoo.com>
Date: Fri, 18 Jun 2004 06:49:23 -0700 (PDT)
Message-ID: <20040618134923.59209.qmail@web50001.mail.yahoo.com>

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

Received on Fri Jun 18 2004 - 08:47:08 CDT