Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
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 Go to next message
brokndodge is currently offline  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 Go to previous message
brokndodge is currently offline  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 
Previous Topic: My Custom Control not seen
Next Topic: Upp .dll
Goto Forum:
  


Current Time: Fri Mar 29 09:18:25 CET 2024

Total time taken to generate the page: 0.01185 seconds