locked
Related database tables RRS feed

  • Question

  • Hi,

     

    I am trying to built a database on a SQL server 2008 and I have a very important question that I cannot find any solution up to know.

    I am using Microsoft SQL server management studio btw.

    Here is the problem:

    I will create 3-4 tables in the database that each one of them will have some fields/entities that are related to the other tables!

    Example:

    Table 1:

    Customer ID

    Customer Name

    Customer Surname

     

    Table 2:

    Purchase ID:

    Customer ID:

    Customer Name:

    etc.. etc.. etc..

     

    Well here is what I want, once I update table 1 and lets say change the name of the customer with ID: 13 I want all the fields related to that customer in the rest of the tables to be updated automatically!

    I have no idea if this is feasible but I think it is! Therefore it will be no need for me to visit each and every table to make changes!

    As I said before I am new in Microsoft SQL databases. Can anybody please guide me a bit? Direct me to a place that the information I want is provided or just write me some single steps that I have to follow and then i can continue on my own.

    Thanks for everyone reading this, hope somebody will be able to help me.

     

    Thanks ;)

    • Moved by Todd McDermid Monday, March 21, 2011 4:52 PM Design Q (From:SQL Server Integration Services)
    Monday, March 21, 2011 4:32 PM

Answers

  • I'm going to move this to a more appropriate forum, so that people there can guide you better than I can.

    A few short comments... First, and most obvious, is that your database design is what's called "denormalized".  That's the opposite of "normalized", and there are degrees of normalization - it's not black and white.  In your case, the problem is that you're storing the same data in two different places that you want to update at the same time.  That's a problem that normalization will solve - only store that value in one place.

    For example, don't put "customer name" in either table.  Only store "customer ID".  Create a new table that stores "customer id" and "customer name" together.  Then you only have one place you need to update the customer name.  When you're retrieving information from your tables and you want the customer name, not the "meaningless" ID, then you need to JOIN tables together to "decode" that ID into a name.  If you find that you're having to do this all the time, you can create a VIEW that does that join for you, and query the view.


    Todd McDermid's Blog Talk to me now on
    Monday, March 21, 2011 4:52 PM
  • The design seems to be incorrect, if you have Name of customer in one table then do not put the name of customer in another table, OK you might have done it for performance improvement, you have normalized it, but do not put name of the customer instead  put Customer ID.

    If you dont want to change your design, then you need to write triggers to update your other tables, which is not good from performance point of view. Now you can compare which one is better writing a trigger or changing the schema to add customer Id in second table, then you can decide better.


    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin
    Tuesday, March 22, 2011 6:02 AM

All replies

  • I'm going to move this to a more appropriate forum, so that people there can guide you better than I can.

    A few short comments... First, and most obvious, is that your database design is what's called "denormalized".  That's the opposite of "normalized", and there are degrees of normalization - it's not black and white.  In your case, the problem is that you're storing the same data in two different places that you want to update at the same time.  That's a problem that normalization will solve - only store that value in one place.

    For example, don't put "customer name" in either table.  Only store "customer ID".  Create a new table that stores "customer id" and "customer name" together.  Then you only have one place you need to update the customer name.  When you're retrieving information from your tables and you want the customer name, not the "meaningless" ID, then you need to JOIN tables together to "decode" that ID into a name.  If you find that you're having to do this all the time, you can create a VIEW that does that join for you, and query the view.


    Todd McDermid's Blog Talk to me now on
    Monday, March 21, 2011 4:52 PM
  • I agree with Todd, that the best option would be to redesign your tables.

    If this is not possible, then you could use an INSTEAD OF TRIGGER.

    This would mean that when you do an update in table one, instead of the update actually happening, the trigger would fire. You would then place code in the trigger that updates both tables.

    Just as a warning, that this solution could have performance implications on a very busy system.

    For more info on triggers, see this link - http://msdn.microsoft.com/en-us/library/ms189799.aspx

    For more info on Normalization, see this link - http://databases.about.com/od/specificproducts/a/normalization.htm

    Hope this helps. :)


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Monday, March 21, 2011 6:53 PM
  • The design seems to be incorrect, if you have Name of customer in one table then do not put the name of customer in another table, OK you might have done it for performance improvement, you have normalized it, but do not put name of the customer instead  put Customer ID.

    If you dont want to change your design, then you need to write triggers to update your other tables, which is not good from performance point of view. Now you can compare which one is better writing a trigger or changing the schema to add customer Id in second table, then you can decide better.


    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin
    Tuesday, March 22, 2011 6:02 AM