Re: how to design db

From: rpost <>
Date: Fri, 12 Dec 2008 12:04:14 +0000 (UTC)
Message-ID: <ghtjvu$2d0v$>

Volker Hetzer wrote:

>> 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
>> It's a GUI application that can be run by multiple user, but only one at
>> a time.


>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.

I'd recommend putting everything into a user-specific database (copying the shared data), and using something embedded (Sqlite, Firebird). That way installation and maintenance is easy (no services), the user data can easily be moved around, e.g. to a different machine, and authentication can be done at the file system level (no separate user/password system required). E.g. if you use Volker's/Bob's approach with Sqlite I don't think you can stop users from peeking at each other's data.

Can users update the shared data, and if they can, do other users need to see these updates? (Doesn't seem likely since in that case you probably want to share them across installations as well ...) Can anything else (upgrades?) update the shared data?

>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.

This (the ability to run queries on combined shared and non-shared data) is a big advantage of both Bob's/Volker's and 'my' approach.

>> 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.

It's easy to implement, except that you'll be needing to replace table-combining SELECTs with nested for-loops on query results, which looks really stupid and doesn't help performance or maintainability.

The few Sqlite apps I've seen use 'my' approach.

Received on Fri Dec 12 2008 - 13:04:14 CET

Original text of this message