July 17, 2009 at 2:37 pm
I have a table called SYS_registry that contains name, value pairs. The primary use of it is to store information that would normally be in lookup tables to try to keep everything organized in one table. It uses a parent id in each record to match up to the parent's registry_id to create a tree structure.
So, one part of the tree could be Pay Types that contains records for Annually, Monthly, Bi-Weekly, Hourly. These sections would be essentially static, meaning they probably won't change very often, and when they do, I'll be the one adding and removing them. It also means that they have the same registry_ids on each instance of our program (Dev, Beta, and Live).
The other use of the registry is for people to be able to set up their own list of values. So, one section might be Keywords where someone can create their own list of keywords that they can attach to other records in the system. The parent_id works the same, but there's also a id to connect the records to the person who created them. These records are obviously much more dynamic, since they can be created and editted at any time, and these records are going to be different on Dev, Beta, and Live.
I already have this in place on all 3 instances of the program. Right now there's only a couple of places in the program that are using the "static" records. Most of the site is still using legacy lookup tables. And then there are 2 sections of "dynamic" lists that have been implemented, although nobody has actually used them on live yet...
That long set up was to ask the question, is there a good way to manage creating new values and sections on my part and keeping them in sync between the different instances? For example, if I look at our Dev server, the MAX(registry_id) is 168, but on Beta it's 182, and Live it's 164. Right now, the best thing I would think would be to insert the new records into the Beta server, and then copy them to Dev and Live, since Beta has the highest MAX(registry_id). But is this a bad habit to get into for when the registry_ids are higher. I'm just worried that the idea won't be scalable.
July 17, 2009 at 2:48 pm
It's not scalable for sure.
You are trying to keep in sync surrogate values that are acting as primary keys. However you are not generating them with in any realistic way. Identities have this issue.
If these are lookup values, why do they need to be the same on your environments? They only matter in terms of something that matches up inside that environment. If you were to restore data to another server, the IDs would matter. If you are trying to debug things, then include a lookup for that value, or build a better structure for your table. Typically if you are sharing name/value pairs for different purposes, you have a third field that describes the purpose.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 17, 2009 at 4:46 pm
The reason I had wanted to keep the values in sync was so I could get a list of values for a dropdown or other list by referencing the parent_id in the application. Also, if there's logic rules in the application like doing something if a certain value is selected, I've hardcoded the registry_id in the logic code.
Based on what I understand from what you're saying, that's where I went wrong with making it not as scalable as it should be. I do have a third description field, but I think maybe I should use the name field as a identifying field, or add a fourth text field. Does that sound like a better solution?
Also, is there any other major issues with what I'm trying to do?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply