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 |
|
dkekesi
Starting Member
38 Posts |
 Posted - 2011-08-10 : 09:32:46
|
| Hi Sirs,I do not know what the correct term is for what I want to achieve, hence the subject. Here it goes.I have an application where a "profile" (let's call it that) can have multiple type of "settings". Each "profile" has similar data structure so they are stored in a single table (let's call it "profile" table). However, each "setting" has a different property set (data structure) so they are stored in different tables (let's call them "settings" tables). The "property" table has an FK column that should reference the PK in one of the "settings" tables. The trick is that I have 8 "settings" tables to choose from and the number is increasing as more settings types are introduced so the referenced PK is not always in the same table.What I wish to achieve is to enforce PK/FK validation in this scenario. It would also be nice if I could use cascade delete as well. Am I asking for the impossible here?I was thinking about adding a column to the "profile" table that contains the table name where the PK resides, but I have no Idea how to enforce PK/FK relation? Also cascade deleting is an issue here but that could be achieved with a SP.Any input is most appreciated.Best Regards,Daniel |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-10 : 09:38:58
|
| Why do you need separate settings table? why is struture for settings changing? Provided its entity why does attribute varies across. Is it not possible to reduce them to common structure? I'm not sure what you mean by settings so if you can provide some sample data to show how the structure varies then that would be great. Certainly its not a good idea to keep on increasing table like this and worry about maintaining the referential integrity. This system will never be scalable.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
dkekesi
Starting Member
38 Posts |
Posted - 2011-08-10 : 11:52:13
|
quote:
Originally posted by visakh16
Why do you need separate settings table? why is struture for settings changing? Provided its entity why does attribute varies across. Is it not possible to reduce them to common structure? I'm not sure what you mean by settings so if you can provide some sample data to show how the structure varies then that would be great. Certainly its not a good idea to keep on increasing table like this and worry about maintaining the referential integrity. This system will never be scalable.
visakh16: First of all thanks for your help. The profile in my application defines data import. One can assign File importer settings, Email importer settings, Web service importer settings, etc. to a profile. Obviously the data structure of File importer settings will be completely different from that of the e-mail importer settings. In case of a File importer all you need to specify is a directory and a flag if subdirs should be included or not. However, in case of e-mail importer settings you have mail server, port, userid, pass, mail folder and whatnot. You can see there's a big difference in data structure and I certainly do not want to put them in one table as that table would be huge (about two hundred columns) and wouldn't also correctly represent the objects. Not to mention that it will mean problems further upstream.The reason I asked the question in the first place is that I'm also worried about referential integrity.Why wouldn't this system be scalable? How do you propose I move on given the fact that I cannot merge all data into one table?Thanks again for your time and help.Best Regards,Daniel |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-08-10 : 13:33:20
|
| You might want to do some searching for Super-type / Sub-type. That might help with your deisgn, if that is what youa re trying to do. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
dkekesi
Starting Member
38 Posts |
Posted - 2011-08-12 : 04:02:09
|
| Thank you both for the suggestions. It takes me time to comprehend these topics. I especially need some time spent with this issue because both suggestions might be applied to my case.Thanks for the ideas once again.Best Regards,Daniel |
|
|
|
|
|
|
|
|
Dynamic foreign key