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?
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
clientno --foreign key
name --foreign key
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?
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.
"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