Home » Community » Newbie corner » [solved, i guess]difficulty with sqlite3 schema
| [solved, i guess]difficulty with sqlite3 schema [message #26230] |
Fri, 16 April 2010 00:15  |
brokndodge
Messages: 8
Registered: March 2010
Location: in front of my computer
|
Promising Member |
|
|
i'm having some trouble with nested tables in an sqlite3 schema file. maybe nested tables isn't the way to go, but i just can't predict the number of needed rows to set my db up any other way.
here is what i have:
TABLE_(prospect_detail)
INT_ (customer_number) PRIMARY_KEY //AUTO_INCREMENT
STRING_ (first_name, 30)
STRING_ (middle_name, 30)
STRING_ (last_name, 30)
STRING_ (suffix_name, 5)
STRING_ (address_1, 50)
STRING_ (address_2, 50)
STRING_ (city, 30)
STRING_ (state, 2)
INT_ (zip)
INT_ARRAY_ (phone_1, 3)
INT_ARRAY_ (phone_2, 3)
INT_ARRAY_ (phone_3, 3)
INT_ARRAY_ (phone_w, 3)
INT_ARRAY_ (ssn, 3)
INT_ (beacon)
TABLE_(documents)
BOOL_ (docs)
END_TABLE
TABLE_(employers)
INT_ (employer_number) PRIMARY_KEY //AUTO_INCREMENT
STRING_ (employer_name, 50)
INT_ARRAY_ (employer_phone_1, 3)
INT_ARRAY_ (employer_phone_2, 3)
STRING_ (contact, 50)
STRING_ (employer_addy_1, 50)
STRING_ (employer_addy_2, 50)
STRING_ (employer_city, 30)
STRING_ (employer_st, 2)
STRING_ (employer_zip, 5)
DATE_ (hire_date)
DATE_ (quit_date)
BOOL_ (is_currant)
DOUBLE_ (gross_monthly_income)
END_TABLE
BOOL_ (is_sales)
BOOL_ (is_service)
TABLE_(vehicles_owned)
STRING_ (vin, 17) PRIMARY_KEY
INT_ (year)
STRING_ (make, 15)
STRING_ (model, 30)
STRING_ (trim_package, 10)
STRING_ (engine, 10)
INT_ (miles)
DOUBLE_ (acv)
DOUBLE_ (show)
END_TABLE
INT_ (wants) //this should be a stock #
END_TABLE
is it possible to nest tables in an sqlite3 schema? how would I go about trying to accomplish this?
[Updated on: Sun, 18 April 2010 04:12] Report message to a moderator |
|
|
|
| Re: difficulty with sqlite3 schema [message #26249 is a reply to message #26230] |
Sun, 18 April 2010 04:09  |
brokndodge
Messages: 8
Registered: March 2010
Location: in front of my computer
|
Promising Member |
|
|
i've studied a couple of large database structures and come up with this solution:
TABLE_(prospect_detail)
INT_ (prospect_id) PRIMARY_KEY AUTO_INCREMENT
STRING_ (first_name, 30)
STRING_ (middle_name, 30)
STRING_ (last_name, 30)
STRING_ (suffix_name, 5)
STRING_ (address_1, 50)
STRING_ (address_2, 50)
STRING_ (city, 30)
STRING_ (state, 2)
INT_ (zip)
INT_ARRAY_ (phone_1, 3)
INT_ARRAY_ (phone_2, 3)
INT_ARRAY_ (phone_3, 3)
INT_ARRAY_ (phone_w, 3)
INT_ARRAY_ (ssn, 3)
INT_ (beacon)
BOOL_ (is_sales)
BOOL_ (is_service)
INT_ (wants) //this should be a stock #
END_TABLE
TABLE_(documents)
INT_ (doc_id) PRIMARY_KEY AUTO_INCREMENT
INT (prospect_id) REFERENCES(prospect_detail.prospect_id)
BLOB_ (docs)
END_TABLE
TABLE_(employers)
INT_ (employer_number) PRIMARY_KEY AUTO_INCREMENT
INT (prospect_id) REFERENCES(prospect_detail.prospect_id)
STRING_ (employer_name, 50)
INT_ARRAY_ (employer_phone_1, 3)
INT_ARRAY_ (employer_phone_2, 3)
STRING_ (contact, 50)
STRING_ (employer_addy_1, 50)
STRING_ (employer_addy_2, 50)
STRING_ (employer_city, 30)
STRING_ (employer_st, 2)
STRING_ (employer_zip, 5)
DATE_ (hire_date)
DATE_ (quit_date)
BOOL_ (is_currant)
DOUBLE_ (gross_monthly_income)
END_TABLE
TABLE_(vehicles_owned)
STRING_ (vin, 17) PRIMARY_KEY
INT (prospect_id) REFERENCES(prospect_detail.prospect_id)
INT_ (year)
STRING_ (make, 15)
STRING_ (model, 30)
STRING_ (trim_package, 10)
STRING_ (engine, 10)
INT_ (miles)
DOUBLE_ (acv)
DOUBLE_ (show)
END_TABLE
|
|
|
|
Goto Forum:
Current Time: Sun Apr 27 22:11:15 CEST 2025
Total time taken to generate the page: 0.00892 seconds
|
Members
Pages
Help
Register
Login
Home
