[EMAIL PROTECTED] wrote: > If I have something like a real estate database where each customer > can have an ordered list of houses they want to visit, is there a > recommended way to design tables that just link to other tables to > create the ordered list? I have a table of houses and a table of > customers. My (probably incorrect) intuition is to create a separate > table for each ordered list the customer wants. But looking at other > databases, I see that people just create one big table for all > customers like this: > > create table customer_list_map ( > customer_id references customers( id ), > house_id references houses( id ), > visit_order integer ); > > Then they do the following to find an ordered, customer-specific house list: > > select * from customer_list_map where customer_id=<some customer id> > order by visit_order > > I don't know anything about databases, but that seems inefficient and > more work to maintain (e.g. if multiple lists per customer are later > supported). Is there a better way to do stuff like this with SQLite? > > One general rule about SQL work is that you don't create tables dynamically. So the 'customer_list_map' idea is the 'proper' way to do it.
Sometimes there are good reasons to create tables dynamically (especially temporary ones), but this requirement certainly isn't one of them, since the 'customer_list_map' is a good solution to the problem. There's a reason everyone else is doing it that way... As long as you have an index on the customer_list_map on the 'customer_id' column, and you have an index on the house list table on the 'house_id' column, then it should be quick. If you are thinking of having multiple lists per customer, then just add a 'list_id' column to the customer_list_map table in anticipation. Then your select can select on that as well as the customer id. You'll find that the 'customer_list_map' way works well, and is actually a lot simpler to handle in the long run than doing it your proposed way. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
