July 22, 2009 at 1:54 am
Hi All,
I dont have great depth of knowledge about table designs, though I understand basic things.
We are working on a big project having many large tables. Database was designed by experts intially, but later on we added many tables, updated schema. At some places, we made mistakes. I do not want to repeat those mistakes again, so wanted to confirm every little thing it may seem.
Here is my problem :
Currently we have one 'Territories' table which has around 4000-5000 records. This table has columns like Id,Name,Description,Level etc. Our system has 6 types of territory levels and each territory has some territory levelID associated with it.
Now, we want to store some preference type of bit flag for level 2 territories only. Currently there are only 6-7 territories of level 2. And even if data grows in near future, these level 2 territories will not be more than 15-20.
In this situation, I feel its impractical to create new column in 'Territories' table itself to store bit flag for only level 2 territories. So I think separate table is needed.
1. Is it good solution to create new table to store only few records ?
2. My colleague created new table, but was storing only the level 2 territories WITH bit flag 1 (ignore records with flag 0) and he had written logic based on that. Which is best method in this case - to store only the bit flag 1 records OR store all level 2 territories with associated flag values.
3. In 2nd approach while storing the values, his approach was to delete all records from the table first and then insert all level 2 territories with flag 1. For every update, he used to follow this process. I feel its very bad idea. Reason is that there will be two operation for every single update - delete and insert. Any other disadvantages ?
Please let me know your thoughts/suggestions on above three points. Thanks in advance.
Regrads,
Bhimraj
July 22, 2009 at 10:31 am
based on the information provided I like the solution that is in place if most of your searches are to retrieve territories that have the preference --Create a table that only stores the terriroty id of level 2 territories that have the preference.
If over time the preference may extend to all territory types then you are still ok here you could just change your constraints.
Then if over time if other preferences are introduced you could always refactor into a different model if it made sense.
July 22, 2009 at 12:21 pm
I would respectfully disagree.
If I got it right PreferenceType is an attribute of Territory being "Territory" the entity described by Territories table.
Having said that some territories would have PreferenceType = 1 while other territories would have PreferenceType = 0.
No logical reason not to add PreferenceType to Territories table.
By creating a new PreferenceType table you would end with an ugly 1-to-1/0 relationship in between PreferenceType and Territories table. Remember, each time you see a 1-to-1 relationship in between two tables that either means both tables should be one OR there is a missing table in between the two affected ones.
Bottom line, I would just add PreferenceType column to Territories table.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 22, 2009 at 1:30 pm
I like the bit on the territories table if all the territories are apples.
From what I read it sounded like terrirtories of level 2 were apples and other territories were oranges (level 1) and grapes (level ...)
In which case it may make sense for each level to have its own preferences table / information
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply