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

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.

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.

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?

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.

thank you both. I have a lot of reading to do tonight