Hi,

> > Now I'm not sure about the goodness of this schema. I find lots of
> > problems when I want to create a new client because I have to shutdown
> > the database to create his specific tables.
>
> Why would you have to shutdown?

I've got a table, named CLIENTS, with a primary key field, K_CLIENT.
Every time I add a new client to my database I insert a new record to
this table. The K_CLIENT field is incremented by means of a generator.

After that I have to create this new client's tables. For this purpose I
use the K_CLIENT value as the suffix for the tables' names, as I
explained before. So, if the K_CLIENT value for the created new client
is 14, I will create the table named GROUPS_14 for this client.

To create the tables and their relationships, triggers, constraints and
so on I use a SQL script with the appropiate DDL statements. The point
is, the database user who performs this opertation must have exclusive
access to the database in order to succeed. My database is supposed to
be a 24 hours database, so system users can be connected to the database
server using the database at any time. That is the reason why I have to
shutdown the database.

>
> > I also have problems when I
> > have to modify some metadata because it must be done to every client's
> > tables.
>
> Well, that's life ;)
> You can make scripts that automate it.

I use scripts, but is easier to modify ten tables than one thousand,
even if they all are similar.

>
> > The question is: can anybody tell me if my database design is good
> > enough to keep it or should I change it and avoid repeating tables for
> > every new client?
>
> Are they all using the same database?

Yes.

> Do those tables' structures differ for different clients?
>

At this moment, tables are similar for different clients.

> > Another related issue: sometimes, either if I backup the database or if
> > I create lots of new tables (DDL statements), because I need to create
> > many new clients, I receive the following exception: lock manager out of
> > room.
> >
> > Why this could be happening and what can I do to solve it?
>
> Can you please explain your problem in a little more detail. I still
> don't understand why do you have to create new tables for each client.

Beacause of the need of shutting down the database when creating new
tables, I usually create lots of new tables, just for a future use, to
avoid shutting down the system every time I have to create a new client.
This way I have the infrastructure ready to accept new clients.
Sometimes, in this process, running my DDL script, I receive a "lock
manager out of room" error. This also happens if I back up the database
with gbak.

Thank you in advance.


--
Juan Pedro López Sáez
ALTIRIA TIC, S.L.L.
www.altiria.com