Re: One big database or multiple small databases

[Roland Bouman]

Karel Bokhorst wrote:
> LS,
>
> I have an apllication where a client logs in with
> username and password.
>
> The application is a sort of event manager where
> i need to store the name of the event and the
> dates that the event (re)occurs.
> So my line of thought is to create a table
> "event1" with the name of the event and a table
> "event1_dates" with all the dates ( single days,
> not periods)
> Maximum number of events is for the moment 5. So I
> create the tables "event2" and "event2_dates"
> etc.

You want to create a different table for each event? You probably shouldnt do that. The idea is to give each distinct type of thing it's own table.

> So far so good.
> But now for the next client. Do I create a new
> database for this client (name of the database
> binds to the client_id in the main user_admin
> database) or should I solve this in the same
> database with a construct with tablenames like
> "client_id_event1_date" etc.

No an No. Here, you're dealing with two separate types of objects that are related. This is implemented by adding one ore more columns to one of the tables so that the records in that table can 'point to' the other table.

> If business goes well ;-) i expect up to a 1000
> clients. I have a provider that allows me
> unlimited databases, but not unlimited storage
> space.
>
> Anybody like to comment or share his/her thoughts
> on the pros and cons of either solution.

The disadvantages of having multiple database to perform essentially the same thing are so manifold that it is hard to pick out the best ones. Anyway:
-maintenance. You need to change many databases when the structure of te datamodel changes due to some change in requirements.
-searching. Questions like 'how many events am i handling?'; 'what is the average number of events im handling for a client'? 'Wich events are due tommorrow'? cannot be efficiently answered when the data is scattered over many databases.

As to what you could do, here it goes:

1) create one database where you handle all events for all clients.

2) create one table for the event_types. This will contain one row for each of the events you were talking about (the 5 different events you were talking about)

create table event_type(
id int unsigned not null auto_increment
, name varchar(64) not null
, constraint pk_event_type primary key (id)
, constraint uk_event_type1 unique (name)
);

This will be a list of all the different types of event you are arranging for your clients. (5 different ones so far - yes?)

Now, you already mentioned the name, and I added an Id. The Id is just a 'surrogate key' (google that) - a technical solution to make it easier to work with the database later on. The Id has the auto_increment attribute: you wont have to supply values for that column, mysql will generate unique numbers for it automatically when you perform inserts on the table.
We added a primary key constraint on the ID column. This ensures you can uniquely identify one and only one record from the event_type table using only one single integer. Also, we added a unique constraint or the name column. This ensures that you can never enter a duplicate event type name.
Also, both columns are NOT NULL. That ensures that for each row in the table the name and the id will always contains data - they will never be NULL (NULL is the symbol used to denote 'no data here')

2) create one table for the event_customers. This will contain one row for each of the customers you were talking about (you called them clients - I prefer customer to prevent confusion with the client used in a technical sense - as in client/server)

create table client(
id int unsigned not null auto_increment
, user_name varchar(64) not null
, first_name varchar(64)
, family_name varchar(64)
...
... other stuff you want to keep track of
...
, constraint pk_customer primary key (id)
, constraint uk_customer1 unique (user_name)
);

3) Now, your original event table will look something like this:

create table customer_event(
id int unsigned not null auto_increment
, customer_id int unsigned not null
, event_type_id int unsigned not null
, constraint pk_client_event primary key (id)
, constraint uk_client_event1 unique (customer_id, event_type_id)
)

For each distinct event_type you are handling for a particular customer, you will need a record in this table. The customer_id will be the value of the id for the customer. Likewise the event_type_id will be the value of the id for the event_type. The customer_id effectively points to the customer for which you are handling the event; the event_type_id does the same but for the event.

You can formalize the fact that the customer_id and the event_type_id columns are meant to reference records from the customer and the event_type table respectively. You can (and should) do that using foreign key constraints. (read http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html and http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html)

Foreign keys help you maintain referential integrity: for example, they prevent you from putting a value in the customer_id table that is not present in the id column of the customer table. They can also help you handle what should happen when you delete a customer record for wich referencing records exist in the customer_event table.

(Please read all the stuff about foreign keys yourself - it should get you started to add them yourself.)

The table has a unique constraint over the customer_id and event_type_id combination. (I am assuming you don't want to handle the same event multiple times for a particular customer - I can imagine cases where you would like to be able to allow duplicates - you be the judge of that).

5) Your event dates will indeed become yet another table:

create table client_event_date(
id int unsigned not null auto_increment
, customer_event_id int unsigned not null
, event_date date not null
, recurrence_interval int unsigned
, recurrence_interval_unit_id int unsigned
, constraint pk_client_event_date primary key (id)
, constraint uk_client_event_date1 unique (client_event_id,event_date)
)

(This too would have a foreign key constraint to maintain referential integrity for the reference to customer_event.)

You said something about recurrence. To accomodate that, this table has the recurrence_interval and the recurrence_interval_unit_id. The recurrence_interval_unit_id references a table that holds all the different type of time intervals that you need for the recurrence. You really need that when you want to be able to have recurrence for fixed periods of time (...each 7 days...) and for calendar periods (..each month.. - a month is not a fixed amount of time). For non-recurring event dates, you would just leave these columns blank.

I can imagine that in same cases, recurrence has to do with the event type rather than with a particular client-event combination. If that is the case, the event_type table should be adjusted to hold that kind of information.