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 |
|
sirbijan
Starting Member
1 Post |
 Posted - 2009-04-21 : 04:48:48
|
I am trying to develop a contract generator module for a program. This contract generator is much of a form generator if I wana generalize it, which means the admin should be able to define a contract which is basically a big text, inside this text some fields should be defined, like the client name and the date and time and ... So, we have a many-to-many relationship. A ContractTemplate table which has the template (default text) of a contract, a FieldTemplate table which has all the fields that can be defined within a contract, and a middle table called ContractFieldTemplate which shows what contracts can have which fields. Look: The first 3 tables at the top are the design till now.Now we wana make instances of these contract templates, allocating each instance to numerous users. Till now the admin only defined the template of a contract, but from now on we actually wana fill a contract for a user. So, we create a Contract table which says what ContractTemplate is filled with what user (FK_Appointment_ID). The problem is now this:The admin defined what fields from the FieldsTemplate table are eligible for that type of contract, in the ContractFieldTemplate. With my current design, what you see in the picture, I should read the ContractFieldTemplate for the type of fields that is assigned to a certain type of contract and then programatically present it to the user in a form to fill and then insert those values and their relative field_id into the ContractField table at the very bottom of the picture. What I am trying to say is that I'm not making the constraint at the database level. I dunno how to relate the Contract table to the ContractFieldTemplate, if it was any way, like it becomes impossible for an instance of a contract to have fields not defined in the ContractFieldTemplate table.I know it was a little bit lengthy, but I hope I could make myself clear. |
|
|
gedwards
Starting Member
19 Posts |
Posted - 2009-04-22 : 16:40:31
|
| Instead of the flow from Contract Table to the ContractField table and then to the FieldsTemplate Table, shouldn't you go from the Contract Table to the ContractFieldTemplate Table?I'd also think that whatever columns are represented in the FieldsTemplate Table ( title, tag, type ) would be also in the ContractField Table. And the value is whatever is in the table rows.For a form presented to the user, the fields should be hidden until a FK_ContractTemplate_ID is filled in. At that point, you would unhide the appropriate fields.I'm much more of a database guy, so I'd be using a contraint. And since I am used to Structure, not Text, so I maybe missing what you're trying to explain.Greg EGreg E |
 |
|
|
|
|
|
|
|
Form generator DB design problem