|Design Advice [message #90435]
||Wed, 03 September 2003 02:34
Registered: October 2001
I am involved in a project and looking for design advice. I will try to explain requirements (long message, sorry!).
Imagine a traditional data entry application but in which the user is able to define dinamically his/her tables and fields.
Clients are win32 written in Delphi. A runtime form designer lets the user add or remove fields from forms.
The application is multiuser and should allow the admin to hide some fields or put them read only for a particular user.
Now my problem is the DB schema design.
I choose this approach:
1. I created a kind of data dictionary with tables describing user, roles, logical tables and logical fields. The admin manages these data.
2. A privileged schema holds this data. The other users access these objects only through a package. So they don't see the actual data.
3. Where is the best place to put the "actual" data?
I see two ways:
a. let the application build true, physical tables according to the logical data dictionary
b. use a container table in which all data are stored.
I choose the b. approach but this have some problem.
The client needs to see an "horizontal" data view.
So I use a temporary table in which, every time the client ask for a row, a horizontal view is rebuilded.
Any tips will be very appreciated!!
(Also some advice about any books or online resources about designing a structure like this.)
Thanks in advance