Re: dynamic database concept

Hi,

It is a dangerous thing to allow users to change the structure of a database.

An alternative approach is, to store all values in one table and add someting like a "private data-dictionary". The dictionary Table stores all fieldnames. a second table stores soemthing like "recordnumbers" or "internal kays" . ´The third table stores all field-values.

If the user wants to add a new field to a "virtual table", you add one record to your dictionary table, containing the "fieldname".

If the user wants to store a new record to the "virtual table" he has to specify the fieldnames an values; For each fieldname, the user specified, you add a record to your "big table of fieldvalues".

Example:

TABLE myFieldNames:

"name"
"firstname"
"profession"
"city of birth"

TABLE myRecords

"1";
"25";
"12345";

TABLE myValues:

"1", "name", "Smith";
"1", "firstname", "Jack";
"1", "city of birth" ; "New York"
"25", "name", "Miller";
"25", "firstname", "Ron";
"25", "profession" ; "fitter";
"12345", "name", "Carter";
"12345", "firstname", "Jim";
"12345", "profession" ; "Softwaredeveloper"
"12345", "city of birth" ; "London"

In this example you have stored 3 "virtual records". You can allow the user to add records to the TABLE myFieldNames (but you should not allow him to delete records, or to modify records in this table).

But you should be aware, that with this approach you lose many of the benefits, modern database-systems provide. The SQL-statements, you use in your C++ - programm will not change - even if the user adds fieldnames, but you will have to do a lot of work in order to collect the values for one "virtual record". You will also have to create more kinds of these tables, if you want to store different datatypes like int, float, ...;

Gert

"Mr. Br" <rogbrasil_at_yahoo.com> schrieb im Newsbeitrag news:196e0f42.0307111147.13adc3f0_at_posting.google.com...
> Hi. I am c++ developer and beginner in the database modeling, I am
> using a library in c++ to develop my database.
>
> In fact it is a simple structure, a cadaster of pieces for an
> engineering program.
>
> The problem is that the cadaster should be dynamic, I mean, users have
> the possibility to create new fields through the interface, besides
> those that are already default of the program.
>
> My doubt is, how could I control the creation of those new fields,
> since, after a new field servant, I have that associate it to the
> right information inside the program when it opens the files of data
> again (potency of a lamp for example, should be attributed to the
> lamp).
> An user " X " can open the cadaster of another user "Y ", and they
> might be of " different versions", then an dynamic integration should
> take place to synchronize those versions, so that a certain
> information "A" won't br attributed to the variable "B" of the program
> and vice-versa.
> Have you understood what I meant ?!
> Thank for any contribution.
> [] `s
>
> Mr.Br.
Received on Sat Jul 12 2003 - 14:31:53 CEST