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?
Each of these customer lists also has a Unicode name and it seems like
table names are supposed to be ASCII so I suspect I'll need another
table for all the lists that the customer has (with the Unicode name
and any other attributes) and then somehow link each table of
house_id's and visit_order's to that table. That's where I get
confused. How would I link an entire table (as opposed to a row in a
table) so I can say "select * from <the customer-specific table x>"?
Do I just name the table with the unique id from the table of list
names?
Am I just going about this all wrong?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
