Results 1 to 6 of 6

Thread: database design

  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Talking database design

    Hello, I'm new and new to creating databases. I studied others in the past and I have what I hope is a simple design question. But first of all, thanks in advance for helping me out.

    I'm trying to create a client database for my boss but I'm running into an issue. I have created a "client" table, a table of "personal" info about the client and a "goals"/works in progress table.

    In each table I have created a row for a customer number as the primary key and linked it back to the "client" table which is also the primary in the client table.

    One of the things my boss wants to include in the goals table is what products the client has already purchased and the product item number for each product already purchased. My question is this: If A client has purchased item 001,002,003,085 and 151 and client B has purchased 151 and 003 and client C has purchased 085; because it's possible to have a client purchasing multiple items and multiple items being purchased by multiple clients another table of "purchase history" is not going to be enough, is it?

    Do I need to create another database of all item numbers and products?
    Is so how do I then set up the "purchase history" table within the 1st db that I started?

    Thanks, again.
    Pepatti

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You have mentioned 3 entities: clients, products and purchases. (I'm ignoring "goals" because that doesn't seem to be relevant here). So that's 3 tables:

    create table clients (client_code primary key, ...);
    create table products (product_code primary key, ...);
    create table purchases (purchase_id primary key, client_code references clients, product_code references products, purchase_date date, ...);


    For your examples:

    insert into clients (client_code, ...) values ('A', ...);
    insert into clients (client_code, ...) values ('B', ...);
    insert into clients (client_code, ...) values ('C', ...);

    insert into products (product_code, ...) values ('001', ...);
    insert into products (product_code, ...) values ('002', ...);
    insert into products (product_code, ...) values ('003', ...);
    ... etc.

    insert into purchases (client_code, product_code, ...) values ('A', '001', ...);
    insert into purchases (client_code, product_code, ...) values ('A', '002', ...);
    insert into purchases (client_code, product_code, ...) values ('A', '003', ...);
    ... etc.

  3. #3
    Join Date
    Sep 2006
    Posts
    3

    database design

    Thank you, andrewst, very much for helping me out, but I think you mis-understood my problem. The three tables are clients (which contains contact info), personal (which contains more seperate info about clients--we wanted seperate from contact info) and goals ( which contains info about client needs, objectives, experience, time lines and funds)

    It is within the "goals" that my boss wanted to put product type (prod_type) and the products purchased. My boss is not that familiar with db's and didn't realize he was asking for all these products to be in one row. So, going back to my earlier question, if one client can purchase multiple products and each product can be purchased by multiple clients, what's the best way to handle that kind of a situation?

    I do not want to have one row with a list of all the products a particular client has purchased because I can see a need to sort based on the product itself (for instance all the customers who purchased xyz fund--one fund purchased by 3 different clients gets 3 different fund id numbers--one for each client)

    Below is my thoughts thus far:
    tbl_client
    clientno --primary key
    name
    account type
    address
    city
    state
    zip
    home_phone
    work_phone

    tbl_personal
    clientno --foreign key
    name --foreign key
    id
    doc_type
    employer
    business
    position
    worth
    income
    tax_bracket

    tbl_goals
    clientno --foreign key
    name --foreign key
    experience
    objectives
    tollerance
    timing
    acct_type
    source
    prod_type??
    products??

    I hope this helps clarify.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I stand by my previous answer: the client purchase history does not belong in the "goals" table, it requires a table of its own - for the reasons you gave yourself, among others. I'm not at all sure what "goals" is meant to be - it seems to be an attempt to "genericise" everything pertaining to a client into a single table, which is dubious to say the least.

    And why do you have "name -- foreign key" in every table when it is the clientno alone that identifies a client?

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    No. You misunderstand.

    Andrewst has a table with ONE record for each product. No more, and no less.

    He has a table for clients.

    And he an INTERSECTION table to define how the clients are related to the products they have purchased. This table, he called purchases.

    There is one-many join between clients and purchases, AND there is a one-to-many join between products and Purchases. This is the way that you define a many-to-many relationship in a relational database.

    The purchases table holds one record for each client-product relationship (purchase) at a given time.

    In addition to the primary key in the purchases table (which is optional) you should create a unique key for this table as well, based on clientID, ProductID, and Purchase_Date.

    Ref the database installments in this thread...at a sister site.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Sep 2006
    Posts
    3
    thank you both. I have a lot of reading to do tonight

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •