?
Solved

DeNormalise data - easy?!

Posted on 2004-03-22
9
Medium Priority
?
287 Views
Last Modified: 2012-06-27
I have an old database, currently it stores customer data (along with contact details) and in a related table (on customersID) stores alternate contact information (name, phone email). There are usually 0-3 of these alternate contacts for each customer, though sometimes there are more.

In my new system direct in the customers table there is space for three alternate contacts, which can be filled in or left blank as required. How can I transfer the old format all into one customers table that includes room for the alt contacts - in other words denormalise the data (in a way anyway). Remembering that current customers may have no alternate contacts or over three (in which case the 4th, 5th etc are just disguarded).


Thanks...
0
Comment
Question by:niico
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10650826
I could ask why denormalise...?

but

insert into newcustomer
  (column list)
 Select column List
          .A.name,a.phone
          ,A1.name,A1.phone
          ,A2.name,A2.phone  
  From OldCustomer as O
  Left Outer Join AltContact as A
   on O.CustomerID = A.CustomerID
 Left Outer Join    AltContact as A1
   on O.CustomerID = A1.CustomerID
 and A1.Name > A.Name
 Left Outer Join    AltContact as A2
   on O.CustomerID = A2.CustomerID
 and A2.Name > A1.Name

should do it...
0
 

Author Comment

by:niico
ID: 10651064
Thanks

Im denormalising basically because the customers table is queried thousands of times a day - to pull records from two tables will hit performance, and complicate things a lot more than pulling from one no?

ok ill give it a go!...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10651264
not necessarily

its a trade off, you've just made the customer row much bigger than necessary , and subject to more update activity
which means less efficient I/O overrall...

how often does the contact information need to be referenced when you reference the customer?

how was the contact information clustered?
a half way house would be to hold an indicator on customer to say that contact information was actually present
or implement the customer / contact information as a view (like my select)

do you perform name searches against this customer table?
  if you do I'd expect performance to markedly decrease (depending on your server configuration)


did you do any performance analysis before you considered this route?

   
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:niico
ID: 10656342
in this case it 'felt' like the right thing to do. Everytime the customer record is accessed there will need to be a scan to at least check if alternate contacts exist (your indicator is a good idea but adds even more complexity). Then if they are added or updated it adds to the complexity of the update a lot as well.

It speeds development and simplifies updates. Also we've decided that three alternates is all that's necessary - if we go the normalised route there could be 0-any number of alternatives (though we could artificially limit this)> this requires additional interface complexity etc - same with updating.

ultimately a select with a join will be slower than a select with no join (if the combined row size is the same) - no?

Yes like you say there is a downside with larger row - but overall it makes things a lot easier (performance hasn't been a problem - like you say some analysis would be the absolute best route but in this case is probably not necessary).

Thanks for views - I'll get back to you on the solution.
0
 

Author Comment

by:niico
ID: 10657101
its working but creating a new row for each alternate customer id and putting all id's in the a1name column - any ideas? thanks...
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 225 total points
ID: 10660624
sorry it should look more like this...

do you have an actual key on the altcontacts table that can be used otherwise the code gets quite
complex trying to just picK a consistent 3 contacts....
 

insert into newcustomer
  (column list)
 Select column List
          .A.name,a.phone
          ,A1.name,A1.phone
          ,A2.name,A2.phone  
  From OldCustomer as O
  Left Outer Join AltContact as A
   on O.CustomerID = A.CustomerID
 Left Outer Join    AltContact as A1
   on O.CustomerID = A1.CustomerID
 and A1.Name > A.Name
 Left Outer Join    AltContact as A2
   on O.CustomerID = A2.CustomerID
 and ( A2.Name < A1.Name and A.2.name > A.Name)
     
  Where ( A.customerid is null
         or( A.Name = (select min(name) from altcontact as x
                               where x.customerid=a.customerid)
              and a.phone = (select min(phone) from altcontact as x
                               where x.customerid=a.customerid
                                     and x.name = a.name)
             ))
and
( A1.customerid is null
         or( A1.Name = (select max(name) from altcontact as x
                               where x.customerid=a1.customerid)
              and a1.phone = (select max(phone) from altcontact as x
                               where x.customerid=a1.customerid
                                     and x.name = a1.name)
             ))
0
 

Author Comment

by:niico
ID: 10665630
hi - thanks again

There is a key on the alt contacts table (altid, int) (if not I could add one)> is the above code for without a key?

would a cursor be easier for this? As its code I only need to run once then its done?...
0
 

Author Comment

by:niico
ID: 10668140
also - out of interest, the (column list) select column List - parts of the sql didnt work, I had to remove them to execute the code. This is sql2k sql isnt it?
0
 

Author Comment

by:niico
ID: 10677782
thanks for the input - you seemed to be on the right track. As it was a one off job and I needed it to be done fast I just used a cursor in the end and its worked - thanks again though.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question