> > I'd like to create another table with > > "AverageSalaries" combining the ID's from (a) and (b) > > into a unique 8 byte ID. I'm thinking that the > > columns would be "SuperID" (8 bytes integer created by > > combining each ID from the city table with the ID from > > each job table and "AveSalary" which is the average > > salary for that job in the given city (based on the > > That seems bizarre. Typical RDBMS practice would be to just use a > two-column composite primary key.
yes, a composite primary key is to be recommended - I would imagine construction of this hybrid key would slow the process of insertion into your database while not improving data retrieval significantly (in looking for one key rather than two). You could view your "Average Saleries" table as a temporary table, and having your averages stored there should speed performance up without any extra key tinkering... Just thinking more into the notion of constructing a hybrid ID on your own - this would greatly reduce the number of available keys for table A and table B. Consider if the implementation took 4 bytes from each table to build a composite 8 byte key. Limiting a primary key to 4 bytes restricts it to ~4.3 Billion (2^32) possible rows for that table. This may seem like alot (and should be for the purposes of sqlite!), but with a full 8 byte key you have ~18 Quadrillion (2^64) possible keys available, which is 4,294,967,296 times more, if I'm not mistaken. > It just seems that with as many as > ten thousand item in each of these tables, it would be > much quicker to create this "combinedID" rather than > select the item from a table based on comparing ID's > from two other tables individually. By combining the > 2 4-byte ID's, I can create a unique 8-byte ID which > should be accessed quicker. Indexing your table properly should eliminate the need to do this. The speed of looking up keys shouldn't be slowing your application down for an indexed table... When you think about it, the hybrid key won't speed up the actual data acquisition involving the transfering of rows from your database to memory for output/processing. And then you'd have the problem of having to de-compose the hybrid key if you had to re-locate the originating table IDs, which would create alot more overhead than any pefromance gains... Clever idea, though. ccl
