locked
Help with Designing Relational Database RRS feed

  • Question

  • I'm trying to create a simple customer tracking program and need help designing the database. I've created three tables (Table1, Table2, and Table3) in a database (Customers). My tables' columns are designed as follows:

    Note: I split the customer information into multiple tables to prevent database bloat because Table2 and Table3 aren't going to be accessed often by the user. (I'm trying to link the same CustomerID to all three tables. Does this even matter anymore? I'm coming from dbase programming.)

    Table1:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    FirstName     nvarchar(15)     NULL
    LastName     nvarchar(15)     NULL
    Address     nvarchar(40)     NULL

    Table2:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    AccountNumber     nvarchar(10)     NULL
    AccountType     nvarchar(10)     NULL

    Table3:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    Notes1     nvarchar(MAX)     NULL
    Notes2     nvarchar(MAX)     NULL

    My foreign keys are as follows:

    FK_Table2_Table1
    FK_Table3_Table2

    Am I doing this right, because it doesn't seem to be working. Can someone tell me why?

    Sunday, August 22, 2010 2:31 PM

Answers

  • Don't split the table like that.
     
    In your table2, the CustomerID is primary key, so can't be listed more than once. That means no customer can have 2 or more accounts. Is that what you really intend?
     
    If it is, just put the details into Table1. If you could one day have a customer with multiple accounts, then you do need Table2, but you should not have CustomerID as primary key.
     
    Similarly for table3. If you could need multiple notes for a customer (e.g. creating different comments on different dates), then you need Table3 but CustomerID should not be primary key. If you will never need multiple notes per customer, put the fields into Table1 and lose Table3.

    --
    Allen Browne - Microsoft MVP.  Perth, Western Australia
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    I'm trying to create a simple customer tracking program and need help designing the database(s). I've created three tables (Table1, Table2, and Table3) in a database (Customers). My tables' columns are designed as follows:

    Note: I split the customer information into multiple tables to prevent database bloat because Table2 and Table3 aren't going to be accessed often by the user. (I'm trying to link the same CustomerID to all three tables. Does this even matter anymore? I'm coming from dbase programming.)

    Table1:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    FirstName     nvarchar(15)     NULL
    LastName     nvarchar(15)     NULL
    Address     nvarchar(40)     NULL

    Table2:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    AccountNumber     nvarchar(10)     NULL
    AccountType     nvarchar(10)     NULL

    Table3:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    Notes1     nvarchar(MAX)     NULL
    Notes2     nvarchar(MAX)     NULL

    My foreign keys are as follows:

    FK_Table2_Table1
    FK_Table3_Table2

    Am I doing this right, because it doesn't seem to be working. Can someone tell me why?


    Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to forum, rather than allenbrowne at mvps dot org.
    Sunday, August 22, 2010 3:05 PM

All replies

  • Don't split the table like that.
     
    In your table2, the CustomerID is primary key, so can't be listed more than once. That means no customer can have 2 or more accounts. Is that what you really intend?
     
    If it is, just put the details into Table1. If you could one day have a customer with multiple accounts, then you do need Table2, but you should not have CustomerID as primary key.
     
    Similarly for table3. If you could need multiple notes for a customer (e.g. creating different comments on different dates), then you need Table3 but CustomerID should not be primary key. If you will never need multiple notes per customer, put the fields into Table1 and lose Table3.

    --
    Allen Browne - Microsoft MVP.  Perth, Western Australia
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    I'm trying to create a simple customer tracking program and need help designing the database(s). I've created three tables (Table1, Table2, and Table3) in a database (Customers). My tables' columns are designed as follows:

    Note: I split the customer information into multiple tables to prevent database bloat because Table2 and Table3 aren't going to be accessed often by the user. (I'm trying to link the same CustomerID to all three tables. Does this even matter anymore? I'm coming from dbase programming.)

    Table1:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    FirstName     nvarchar(15)     NULL
    LastName     nvarchar(15)     NULL
    Address     nvarchar(40)     NULL

    Table2:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    AccountNumber     nvarchar(10)     NULL
    AccountType     nvarchar(10)     NULL

    Table3:

    (PK) CustomerID     int     NOT NULL     IDENTITY(1)
    Notes1     nvarchar(MAX)     NULL
    Notes2     nvarchar(MAX)     NULL

    My foreign keys are as follows:

    FK_Table2_Table1
    FK_Table3_Table2

    Am I doing this right, because it doesn't seem to be working. Can someone tell me why?


    Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to forum, rather than allenbrowne at mvps dot org.
    Sunday, August 22, 2010 3:05 PM
  •  
    Allen's comments are spot on, as usual.
     
    Also-
     
    If you were intending 1-to-1 relationships, yours wouldn't work
    correctly anyway because your Table2 and Table3 specify Identity on
    the primary keys, which means they will generate their own automatic
    values and will not match the values in Table1.  Well, they might
    match some by accident, but that's not what you intend.  If you really
    wanted 1-to-1 relationships (which are not justified in your case),
    you would not have Identity on tables 2 & 3.
     
    And you mentioned trying to prevent database bloat.  Users merely
    accessing tables doesn't cause bloat.  Having some fields in a table
    that are often Null isn't a problem in database design unless they are
    excessive.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    Sunday, August 22, 2010 3:15 PM
  • Can one customer have more than 1 address? Or can the same address be shared between two or more customers? If yes, then you may want to create Address table separately and also create a junction table CustomerAddress where you will have CustomerID FK and AddressID FK.

    The other points were already discussed: CustomerAccounts and CustomerNotes should have CustomerID as a foreign key and have their own unique key as primary key.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, August 22, 2010 4:03 PM
    Answerer