Richard Cooke wrote: > Can I use the user_id (as a FOREIGN KEY) that resides in another > database to link the two DBs together? I'm a newbie to all of theis > database design so I am using this project as a learning experience. > From my limited knowledge of MySQL I think I could have everything in > one database and just have a bunch of tables. If I use SQLite I'd have > a bunch of files (or smaller databases). If there was a problem I can > see just losing one ueser's data and not the whole "ball" of data with > all the user's data.
This doesn't answer your main question exactly, but ... A general point of database design is that whenever you have a bunch of entities where some of those entities are defined in terms of others, including some being constrained in terms of others, such as with a FOREIGN KEY, then it only makes sense to have all of those entities collected into the same single database. With SQLite, that means put everything in a single file. The main reason for this is that you can then take any single database in isolation from others and it is self-describing enough that you can properly interpret what you see in that database, including what constraints it has. With SQLite, where its files can be located anywhere, there is no system restriction keeping your related files together, so you don't want to use them such that no part of your database can be used if you easily don't have all the parts. In your case, I would put everything, all your tables, in a single SQLite database file, if you use SQLite, same as you'd use a single database if you use PostgreSQL or other options instead. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
