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 |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2004-10-13 : 02:49:25
|
You have a set of tables, let say they represent a simple (extremely simplified) view of a party structure:Telephone>--Party--<Address So party has a simple structure of:UID int identity(1,1) not null,FirstName varchar(50) not null,Surname varchar(50) not null,BirthDate datetime null,HighSchool varchar(50) nullAddress has:UID int identity(1,1) not null,Party_UID_FK int not null,AddressLine1 varchar(75) null,AddressLine2 varchar(75) null,AddressLine3 varchar(75) null,AddressLine4 varchar(75) null,AddressCode varchar(10)Telephone hasUID int identity(1,1) not null,Party_UID_FK int not null,PhoneType varchar(15) not null, PhoneCodeNumber smallint null,PhoneNumber integer nullSo, from a very simplistic front end, they are showing the complete PartyDetails. They display all fields.Now, they want the update/edit functionality, so they want to click their "EDIT" button, go through several of the values, and change them, then "SAVE".Their design says that they have a simple update statement, and they update all fields (13 in our case, across 3 tables), regardless of whether they changed or not. I am not keen - I say update only what has changed. Their counter is that (a) that is a lot of code and any time saved on the DB is lost in the code (b) it becomes more complex.Further to this, though, they say, we should not have 3 tables, but 1 table, so that we call 1 SP, and it does the updates (of all columns). Alternatively, they say have all 3 tables, but create a row for address and for telephone, even if we don't have the data, so that they can just update it later, rather than having to determine whether to delete, insert or update.How do I continue this design discussion so that we get to the following principles:a normalized database structuredo only the work you need to (i.e. update only the changed data, insert only actual values).Am I wrong here I am struggling to convince them... *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
DavidD
Yak Posting Veteran
73 Posts |
Posted - 2004-10-13 : 03:16:35
|
| We have a similar setup and having been involved in the backend and the frontend I can say that it is much easier to mark a record as being changed than try to keep track of every field. We do use a normalized database though, and changes to records only affect that table they belong to. Perhaps this could be a compromise calling 3 edit procs instead of one will enable you to keep your database structure and not make them work too hard!RegardsDavid |
 |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-13 : 06:40:30
|
quote:
Originally posted by Wanderer
How do I continue this design discussion so that we get to the following principles:a normalized database structuredo only the work you need to (i.e. update only the changed data, insert only actual values).
Normalised structure : No doubts about that (atleast to me).Do only the work you need.. : This could be evaluated againest the requirements and performance impact. If logging/tracking the changes is not in scope and updating all 13 columns (even if only one is changed) is not causing any performance impact (page splits and updating indexes etc..), I'd say, we can loosen up a bit on the developer. This would reduce a lot of coding and might even run faster. Better yet (if at all), update only the table(s) whose columns are changed.hthHemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have ONE each.Exchange an Idea, we have TWO each. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-13 : 06:43:35
|
| I have SAVE SProcs, mechanically generated, that are happy to receive any/all columns in the table. Any parameter which is NULL causes the column to be left alone, with its original value; a parameter which is an empty string causes the column to be set to NULL, and any other value becomes the new value for that column.Parameters to the SProc default to NULL, so any that are missed off by the caller cause those columns to remain "untouched"The SProcs Insert if not found, otherwise update. There is an optional parameter which enforces "Must NOT already exist" or "Must ALREADY exist".We developed this for Web FORMs where each client tends to want to "leave off" some of the columns in the underlying tables, or move them from the Step 1 page to Step 2 etc. etc.I can give you more details if its of interest ... hell, I might even do the first article for my blog ...Kristen |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-13 : 07:18:07
|
Kristen.... colour me interested...  *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-13 : 09:14:01
|
| This might sound "unnormal" -- but my take is this:The Party record is created when you create a new Party, and only that record. If you add an address, then a row gets added to the address table -- but not until you explicitly indicate you are entering or creating an address. Same with the phone numbers. Then, if one exists and you are editing (or deleting) it, then you are explicitly editing (or deleting) an existing phone # or address.Now you might say -- phone # and address are required, but they can have multiple ones. Perhaps a "primary" phone and a "primary" address must be present. Iff this is true, RI makes it tough to indicate a child record MUST exist in a related table, so I feel the best way to structure the data might be:Party Table----------PartyIDPartyColumns....PrimaryAddress1PrimaryAddress2PrimaryCityPrimaryStatePrimaryPhone#In which case the columns are all required and are explicitly created when you create a new party. Then, in your app, you have the option of creating "additional" phone #'s or "additonal" addresses, in the related tables (much as you have it now).Again, this might seem "unnormal", but it does make sense and allows simple constraints to ensure your data is complete, and also provides some potential solutions to some of the issues you are having. It allows for all actions to be explicit -- you create/edit/delete a Party and the primary address/phone, and you create/edit/delete the secondary addresses or the secondary phones in seperate actions.- Jeff |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-13 : 09:53:57
|
| Thanks for your response Jeff.From a modelling point, obviously we can indicate:Address>|-----|-Party-|-----|<TelephoneWhich would indicate a party has 1 or more Addresses, and one or more telephone numbers. Taking the example: given the need to have multiple phone numbers, and the very likely requirement to change that primary over time, I would rather have an indicator on the "telephone" record identifying primary (IsPrimary Bit NOT NULL, default 0 --0 meaning false).True, this doesn't enforce the required nature of the telephone and address when processing the insert - I would say that that has to do with the validation of data against business rules, that should happen before data is inserted. But there are, as always, many ways...Regards the processing of the "edit" - am I correct in understanding that basically, you would propose an "add address", and a "remove address" button, for example, thus actively specifying the creation of an address, and not going the "update if exists, else insert" route.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-13 : 11:46:36
|
| yeah, basically. keep it consistent with the DB design. There must be a reason that the address is kept separate from the Party from a logical design perspective (i.e., multiple addresses are possible), so it would make sense to have the UI set up based on that as well.- Jeff |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-10-13 : 11:54:43
|
| I absolutely agree with you Jeff. Having worked extensively with a highly normalised object-oriented back-end that has been 'shoe-horned' into an overly simplistic front-end, I find that we are constantly having to second-guess the user's intention. I.e. does that 'update' mean that there was a typo when that phone number was first entered, that the number is no longer in service and has been replaced with the one overwriting it, or that the party has an additional number that they now wish to be contacted on?Much better to be explicit from the word go.Mark |
|
|
|
|
|
|
|
|
So ... how do I convince...