Dynamic Table. Worth It ?

[kaixa]

Do you guys think using dynamic table worth it ?

i have data of stuff. since i don't know how many type of stuff the user wants to put on the database, my approach is using the dynamic table where each table list all the stuff according to its type.

i know i can use only one table that list many stuff and use type as one of its column to sort it out.

But, this one table approach have its flaws (i think). Which is :
1. It's not normalize. Since i can create separate table by using dynamic table approach for each type of stuff.
2. When user inputing new stuff on the table, the table will be locked. other user can't read the table when at this state.
3. The table will grow too big (is this bad ?)

well, should i use that dynamic table approach or just one big table ?

Thanx :)

[davide++]

... since i don't know how many type of stuff the user wants to put on the database...

well, should i use that dynamic table approach or just one big table ?

Thanx :)

This mean that you don't know the domain of problem; so, it's very hard to design the database correctly...

Tables define entities, real or abstract: people, cars, clients, orders, cats, and if you put cats and dogs into same table they'll quarrel ;)

A way to design a database which holds any kind of data is to use a set of tables that reproduce the data dictionary (meta-table): objects, object's field and values for field.
For example:
Table OBJECTS: defines a generic object.
OBJECT_ID
OBJECT_NAME
Table FIELDS: defines the fields for each object.
OBJECT_ID
FIELD_ID
FIELD_NAME
FIELD_TYPE
OTHER...
Table VALUES: defines the values assigned to a field of table
OBJECT_ID
FIELD_ID
VALUE.
This approach is normalized and works fine; but there are a lot of problems: first it's hard to manage (think what one have to do to find a value), then these tables tend to grow too big very quickly.

[kaixa]

thanx for the reply.

btw, is it normal for one table to have millions of entries after normalization ?

i know some internet forum that haves about 14 million post on its table

[davide++]

Hi all.

When a table contains millions of record is a big table; the biggest table I've seen contained about 70 millions of record.
Usually when a table tend to grow too much it is splitted in two or more identical tables.

[exterminator]

When data grows up very large - unmanageable on disk memory - you archive them onto tapes... and dump them into store rooms.. :)

It is always better to keep similar stuff together, isn't it? Otherwise, consider how much of a problem can it be to keep sugar and salt together in one container. Not that it is impossible to seperate them, it just would take enormous time and effort. That is why grouping types together helps.

If you think normalization is not required, don't do it but that is a rare situation and you should be very sure. People even dump huge XMLs onto tables as BLOBs. But still, it is better to keep similar blobs together, if you can understand what I mean.

By the way, what do you mean by dynamic table and what are you comparing it with? I think you might have got the terminologies incorrect, but I am not very sure.

[davide++]

When data grows up very large - unmanageable on disk memory - you archive them onto tapes... and dump them into store rooms.. :)


Yes, you can do it when you don't need all data at run time. Otherwise, you must choose other solutions, such as splitting tables.

[exterminator]

What is splitting tables? How would you query data? Archiving is done when there is very old data that is not needed immediately. And not all data is required at runtime. If one feels that a part of the data may be required - then probably not archive it.

[davide++]

Splitting table means to define two (or more) identical tables (with same structure, fields, indexes and so on) instead of only one table: in this way you can share out the records (accordingly some criteria) onto more tables rather than put all records into one table, so you'll get smallest tables that are easier to manage.

There are some circumstances where you need all data at run time.
At the moment I'm working on a provisioning system that manages orders of phone services: there are chains of orders, where the first order starts the service and the following orders change the service, but each order needs data of previous orders to define the changes, so I must keep all data into database.

[exterminator]

Never used or seen people using that technique. What can be the advantages and disadvantages of that?

[davide++]

Well.
The main advantage is that you can get small tables without move data outside the database.
The main (and remarkable) disadvantage is that you denormalize database, because this technique requires introducing duplicate structures, so it gives birth to some problems: for example, you must be careful about the criteria used to manage records (a common mistake is to put a row into the wrong table); certainly this is a unnatural database design.

In the previous post I said "Usually when a table tend to grow too much it is splitted in two or more identical tables." Maybe the term "usually" is wrong: this isn't a common rule to design database, on the contrary it's a special rule that one can use in special circumstances, such as when it's impossible to store data onto tapes or external devices. I agree that in most cases one doesn't need all data at runtime, so is correct (and usefull) moving the unused data.