Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2004-11-04 : 06:32:14
|
| I am making a Workflow management system. Because it has to be generic (database as well as the application), I'm having some issues. I can make a table for Persons, Companies, Media (Telephone, Mobiles, etc.), Addresses, Processes, etc.But what I like to do is making one table Objects and the objecttype: Person, Media, Address, Process, etc. Though I still need tables for the specific object information (A person has different fields than an Address), All objects have a record in the table Objects.What will happen is that the table objects will grow huge, and I'm wondering if this is good practice? The approach has good benefits. Attributes like "Active", "CreationDate", "Status", "StatusDate" etc. have only one place in the table Objects, and not in the tables of the various object types.Another good thing is the links between these objects. I only need one table to attach objects to objects. Instead of numerous tables to make relations between persons, media, addresses etc. But I don't know if this has a performance penalty?Please throw in your two cents. And if you have articles discussing databasemodels, I like to read them.Thx!Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-04 : 06:45:39
|
| Well you only need one table and three fields in that table to build any system - but that's not really the way relational databases are meant to work.>> I only need one table to attach objects to objectsBut you will also need the object type.You are using derived subsets of the objects table to act as different tables.You could create views on that table joined to the data tables to emulate the actual tables - but why not make them tables rather than views.This sort of thing is usually done to allow the user to extend a database by having the metadata in a table and referencing the data values.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-11-04 : 06:58:47
|
| Thx Nigel,Do you recommend not to proceed in this 'generic' datamodel? What could be a alternative? Should I make an Object layer in the application itself? The beauty of generic is that a user of the system can make his own objects (and his own custumizable table with attribute fields as long as this table is stored in the database for reference).If the user (customer) wants an object 'Sickleave' he can have it without me as programmer knowing it. All sorts of customers could use one and the same system. I even made the Right system generic. The customer can creates his own type of rights (a user can only see his own orders, while a controller can see all the orders of a division, and a DBA has the right to see and delete all orders, but cannot see the value Order Value).Among headaches, the biggest one has to do with performance. There has to be so much done, that I'm afraid building a screen, getting values, checking rights (on every field), displaying buttons with different functionalities (which are stored in the database as well) is making every system slow, or do you reckon in can be done??Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-04 : 07:58:44
|
| That's how a lot of crm systems work but they usually have a core structure and allow users to add fields to screens by the method you are describing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
One or more tables