Re: how to design db
Date: Thu, 11 Dec 2008 22:33:01 +0100
Message-ID: <ghs0us$kfq$1_at_nntp.fujitsu-siemens.com>
ilochab schrieb:
> Il Mon, 08 Dec 2008 20:29:16 +0100, Volker Hetzer ha scritto:
>
>> ilochab schrieb: >>> I'm using sqlite. >>> >>> I need a DB that contains: >>> - a set of general tables >>> - a variable number of sets of users tables. >>> >>> The first set is accessed by every-one. Each of the users' set 'is >>> identical in structure to the others, but it is specific in content >>> respect to the owner. >>> >>> I can manage authentication through my application. What I don't know >>> how to do in a suitable way is the design of a group of identical >>> tables repeated for each user. >> Bob is right, that's not the way to do it. Instead, you have one table, >> with the user id in one column as part of the key. Then you can have >> views that add a "where user = getusername()" or something to your >> queries and instead-of triggers for DML. If it's not a cellphone, I'd >> suggest you consider a bigger database system like mysql, or sqlserver >> express. >> >> Lots of Greetings! >> Volker
>
> You are both really rigth (of course), but let me specify a little more
> my situation.
>
> My DB is not big, and the application is not a production one (it's just
> a diet journal http://dieta.sw.googlepages.com/).
> It's a GUI application that can be run by multiple user, but only one at
> a time.
The point isn't really size or any prod/dev/fun-label but 1) whether the users share data
2) whether the users share resources, like a database server.
From what you told me I presume that your users share data (the general tables), probably some calorie or vitamin data for the different foodstuffs.
So I would still suggest you put the thing in one centralized database
and your user specific tables have one column more, which is filled
with the username by your application.
If your application controls the sqlite requests this should be easy to
achieve.
If a user wants to see the calories haven taken in total it would
translate into something like
select sum(energytable.calories*meal.amount)
from meal join energytable using (foodname)
where meal.username=<BoundParameter>
where BoundParameter is SQLite's way of putting the username into your sql query.
For inserts it becomes just another value to enter and for update/delete
it goes into the where clause.
> I thought to install the main db (with base data like aliments'
> definitions) nearby the binary code and a db with user's specific data in
> each home directory.
No idea how sqlite does this, there's probably a sqlite forum somewhere.
I'm not impressed by it as it needlessly complicates a lot of things
for the sake of one column.
And, whenever you want to work on the central tables, all referencing
user tables are potential troublespots. Migration to an extended (or reduced)
data model is MUCH easier if you don't have to update and check table sets
all over your system. If it's one database, a lot of checking (like broken
relationships) show up immediately and need to be fixed only once.
Lots of Greetings!
Volker
-- For email replies, please substitute the obvious.Received on Thu Dec 11 2008 - 22:33:01 CET