On 9 Sep 2009, at 6:58pm, Shaun Seckman (Firaxis) wrote: > It seems like the more ideal solution would be to create a third > table for ownership that contains a person ID and a vehicle ID.
I thought that a third table was what you proposed in the first place. I was telling you you didn't need one. > This will result in more tables (significantly more if you're > trying to migrate object oriented data into a relational model) but > is that really a bad thing? Yes. Every extra table is an additional join, view, or temporary table. In addition it's one more thing that cannot be indexed. For instance, suppose you want to look up all the cars belonging to Person #47. If you have the person number in the vehicle table you can do SELECT * FROM vehicles WHERE owner = 47 and type = 'car' and you can make an index on (owner, type) that means the SELECT command can go straight there, so it will be super-fast. Now try doing the same thing if you spin your ownership relation off into a separate table. First you have to do a JOIN, then it's impossible to create an index, so SQL will have to do searching and sorting to find the right records. > I managed to migrate similar data using this tactic and the biggest > issue I've had with it is in the generation of long SQL statements > that contain many inner joins (I really wish there was some syntax > sugar for automatically joining all foreign keys). This can become > tricky if performance is a priority as I found myself needing many > unique SQL statements Absolutely. Joins are necessary in many situations. But joins are what makes things like this slow, and the SELECT commands long and confusing. You want to minimise anything that involves joins, views, virtual tables, etc.. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
