July 8, 2008 at 6:19 pm
Hey Xpert DBAs,
I have a big problem on my hands, of course. lol Also take into consideration that i'm not a dba. Background: Asp.net web app with Sql server 2005 db. This application is already in production. I have a primary key defined for a table made up of the following: AcctID + GroupID. Its works great for a one to one relationship we have setup but now the business has come back and now need to adjust this tables primary key to handle multiples. So we would need same acctID + GroupID + something else to allow us to handle this scenario.
Here are my questions:
1. Being so late into the game meaning this table and others are already using this primary key I would have to take a backup of database drop these tables then create a new primary key. Is going back and remapping the tables that used older primary key a real pain in the butt to do?
2. I would need to take existing records and a give new primary to each one. What is the best way on doing that?
3. What other ways can I accomplish adding multiple records in table with this scenario I'm facing?
4. Does this happen alot in real world where you have to change primary keys in db schema due to business demands?
J
July 9, 2008 at 5:28 am
Without some background of what you are actually storing and how the grandularity has changed, it will be difficult to help.
Usually, it is not really that the entity you have selected is not grandular enough, it is typically that you want to introduce another relationship. If that is the case, you would want to consider adding another table to handle the more grandular attributes rather than repeating the data you already have.
You have an AcctID and a GroupID in your table as your unique identifier. To add an additional level of detail, add another table with AcctID, GroupID, DetailID, and the additional columns that have more detail. All of your existing relationships still work unless they need the additional level of detail - in which case you would join them to the new table.
July 9, 2008 at 6:03 am
Thanks for Replying
Ah thats the thing there isn't more granularity or more detail. What is being asked is
instead of capture one record with same attributes to now capture two or more records with same attributes. So for instance instead of having one record with the same primary key I need to implement a primary key: AcctID + GroupID + something else that will uniquely define the additional records but allow me to insert multiple similar records with maybe a different value for a price column. Hopefully this gives you more insight.
J
July 9, 2008 at 6:11 am
Right, so your price column is your additional grandularity. Normalize your data - don't repeat columns that will always be the same.
I don't have the details, but if you are not adding additional columns, you simply had a pretty bad design issue to begin with.
July 9, 2008 at 8:01 am
As Michael suggested, there should be good design to start with, so you dont fall into the pits later on, Doesnt matter how big or small the project is .. Good Database/application design should your primary concern.normalization Plays(should play) a KEY role while architecting the Project.
Maninder
www.dbanation.com
July 9, 2008 at 10:54 am
I totally agree with you guys. I'm not the developer of this database and just want to clean it up by implementing proper changes to its schema. So from what I am gathering here I should pull out the various attributes(columns) that will change like price etc. and move them to a new table that uses the existing primarykey from original table. This solution would be better on two fronts. One Normalization of database and two not as much work in having to implement new primarykey across tables utilizing primary table. So my queries will have an extra table join upon lookup with primary key. Ok I'll buy that. What about performance? Probably would be hardly noticeable.
Thanks,
J
July 10, 2008 at 1:20 pm
Before I worry about performance, I have some reservations about just adding another table. When you mentioned the idea of perhaps having a price column as part of the primary key, that seemed sufficiently odd to raise the idea of REALLY REALLY NEEDING TO KNOW EXACTLY what the change in primary key is supposed to accomplish, with a real-world example of the new entity that needs to become a part of this. I can't think of a particularly good reason to include a price column in a primary key, so I'm not convinced we have enough detail to make a reasonable judgement as yet. Can you be more specific about the reason behind the need for a change, and detail out how the additional field that's intended to become part of the PK represents a real-world object?
Steve
(aka smunson)
:):):)
jjmraz1 (7/9/2008)
I totally agree with you guys. I'm not the developer of this database and just want to clean it up by implementing proper changes to its schema. So from what I am gathering here I should pull out the various attributes(columns) that will change like price etc. and move them to a new table that uses the existing primarykey from original table. This solution would be better on two fronts. One Normalization of database and two not as much work in having to implement new primarykey across tables utilizing primary table. So my queries will have an extra table join upon lookup with primary key. Ok I'll buy that. What about performance? Probably would be hardly noticeable.Thanks,
J
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply