none
.Net tools and DB normalization RRS feed

  • Question

  • If I have a Phone Table that looks like this:

    pk ClientID

         CellPhone

         HomePhone

         WorkPhone

    I most likely will have blank fields in my table, so I create a table like this:

           PK ClientID

         PhoneType

         PhoneNumber

    So the next question becomes how do I get .Net with its Datasets and Databinders to write One record to the DB if I enter only the cell number, 2 records if I enter the cell and the home number and three records if I enter the cell, Home, and Work numbers?

    Thank You,


    David Linda


    • Edited by David Linda Wednesday, July 17, 2019 4:06 PM
    Wednesday, July 17, 2019 4:05 PM

All replies

  • Hello,

    The approach I would take looks like this.

    If I wanted one contact and get their phone numbers where 2 in the WHERE would be a parameter to a command object.

    SELECT C.ContactIdentifier,
           C.FirstName,
           C.LastName,
           C.ModifiedDate,
           C.InUse,
           CCD.PhoneNumber,
           CCD.PhoneTypeIdenitfier,
           PT.PhoneTypeIdenitfier AS PhoneTypeIdentifier1,
           PT.PhoneTypeDescription
    FROM Contact AS C
         INNER JOIN ContactContactDevices AS CCD ON C.ContactIdentifier = CCD.ContactIdentifier
         INNER JOIN PhoneType AS PT ON CCD.PhoneTypeIdenitfier = PT.PhoneTypeIdenitfier
    WHERE C.ContactIdentifier = 2;

    We can add a new phone number, edit one of the phone numbers, remove or mark it as in active since the keys allow this.

    You might have a list of contacts in a listbox, select one then get details with a SELECT such as above. Again, having the keys we can do whatever is needed.

    Doing this with whatever container e.g. DataSet for this specific task may be overkill. I would use a DataTable or a list meaning create a class which represents the data returned from the SELECT statement.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 17, 2019 5:19 PM
    Moderator