tag:blogger.com,1999:blog-426922399870577072.post4234651413036051359..comments2024-03-28T07:02:31.092-04:00Comments on The Database Programmer: Advanced Table Design: ResolutionsKenDownshttp://www.blogger.com/profile/11117175783163937575noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-426922399870577072.post-80490115258780346462014-01-08T09:48:23.132-05:002014-01-08T09:48:23.132-05:00I realise that this is an old post, but thought I ...I realise that this is an old post, but thought I would add my 2 pennorth (2 cents ;-) In one of the systems I've worked with before, there were a lot of settings that could be configured at different levels: user, question, paper, subject, series or default. Now I'm not saying that every setting was required at that level, but some were. So how about the following pattern. You have a table called Setting with columns: SettingId, Name, Description and possibly a default value. Then you have another table SettingValue which has FkSettingID, FkSettingLevelTypeId and Value which is the actual value. FkSettingLevelTypeId is a foreign key into table SettingLevelType which indicates the level that the setting value refers to. In my case that would be user, question, paper etc, with an indication of the priority of each. You can then create a select statement using 'TOP 1' to retrieve the highest priority value for a particular setting.vipesnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-17402155581735401902008-10-16T07:24:00.000-04:002008-10-16T07:24:00.000-04:00Jeremy: do you have a specific case where you have...Jeremy: do you have a specific case where you have tried it and it is falling down?KenDownshttps://www.blogger.com/profile/11117175783163937575noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-1686839064937294722008-10-15T16:36:00.000-04:002008-10-15T16:36:00.000-04:00I have spent the last hour or so reading your blog...I have spent the last hour or so reading your blog entries and while all the ones I have read so far have been excellent, I think this one falls a little short.<BR/><BR/>This idea sounds simple enough. And it looks like it would work quite well for trivial tasks. But as Chui Tey mentioned what if you are doing this with user preferences. And...<BR/><BR/>What if you have dozens of user preferences? This makes the SQL look quite ugly.<BR/><BR/>What if preferences are added often? Now you have an ugly SQL that you have to change quite often.<BR/><BR/>What if the preferences follow different hierarchys? ...<BR/><BR/>Anyway, thanks for the taking the time to help people create better databases.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-67707669862900435132008-07-07T16:30:00.000-04:002008-07-07T16:30:00.000-04:00>xamdam: Changing rates over time involves adding ...<A HREF="http://www.blogger.com/profile/02376091179295592595" REL="nofollow"><BR/>>xamdam</A>: Changing rates over time involves adding dates to your keys. This is a large topic and one I have in mind for a separate post.<BR/><BR/>But in a nutshell, you add date ranges to your various tables and add filters to your resolution WHERE clauses.KenDownshttps://www.blogger.com/profile/11117175783163937575noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-63612501156328021052008-07-07T16:07:00.000-04:002008-07-07T16:07:00.000-04:00Thanks - this is great info! I was searching (with...Thanks - this is great info! I was searching (without much hope) for pretty much this info, you caught me by surprise ;)<BR/>An interesting complication to this would be a situation where rates change over time (which is the version of the problem I am unfortunately struggling with). Wonder if you have any thoughts on that.Max Khesinhttps://www.blogger.com/profile/02376091179295592595noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-81444973583819980092008-05-14T15:03:00.000-04:002008-05-14T15:03:00.000-04:00jakyra: thanks!jakyra: thanks!KenDownshttps://www.blogger.com/profile/11117175783163937575noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-18252033806608132172008-05-14T14:53:00.000-04:002008-05-14T14:53:00.000-04:00I've got to say, this is probably one of the singl...I've got to say, this is probably one of the single most helpful blog posts I've read in a while. <BR/><BR/>I had no idea this existed. Thanks for this. I love your blog!jakyrahttps://www.blogger.com/profile/01826813470235427187noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-88188424997725291402008-05-07T18:39:00.000-04:002008-05-07T18:39:00.000-04:00I've approached this in the past with a different ...I've approached this in the past with a different solution. In my experience you usually know in advance which tables hold the data with the highest priority. In your example, you're reasonable sure that the order of priority would be:<BR/><BR/>1) rates by activity-customer-employee<BR/>2) rates by activity-employee<BR/>3) rates by activity-customer<BR/><BR/>With that in mind you can ease up on the joins a bit and do:<BR/><BR/>select activity, rate, employee, customer<BR/>from (<BR/>select activity, rate, customer, employee, 1 priority<BR/>from act_emp_cust_rates<BR/>where <...><BR/>union all<BR/>select activity, rate, '' customer, employee, 2 priority<BR/>from act_emp_rates<BR/>where <...><BR/>union all<BR/>select activity, rate, '' customer, '' employee, 3 priority<BR/>from act_cust_rates<BR/>where <...><BR/>) order by priority asc<BR/><BR/>It results in something similar to your first stab query (with blanks for company/employee instead of for rate), but it's much much easier on the database than either version. It can also be embellished further to reduce the unused data, but that starts to reduce the performance.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-45010359988647651172008-05-02T17:59:00.000-04:002008-05-02T17:59:00.000-04:00Chui: very good point. Resolutions show up all ov...Chui: very good point. Resolutions show up all over the place once you know about them.KenDownshttps://www.blogger.com/profile/11117175783163937575noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-13098749182649178842008-05-02T17:01:00.000-04:002008-05-02T17:01:00.000-04:00This pattern is also applicable when determining u...This pattern is also applicable when determining user preferences. Usually it falls back to a group preference, then system preference if a value isn't set.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-62444228300889603552008-04-21T16:04:00.000-04:002008-04-21T16:04:00.000-04:00That's a great trick! Thanks!That's a great trick! Thanks!Unknownhttps://www.blogger.com/profile/02202334166535490106noreply@blogger.com