Best aproach for tables structure

[mane_uk]

Hi all,

I am struggling to find the best approach (pros and cons) to a table structure and I hope I can get some opinions!!

I have to record (and be able to update) monthly individual targets for New Business and Renewal to all our clients on our 6 products.

My first thought was:
tblTarget: targetID, clientID, month, year
tblProd1: prod1ID, targetNewBus, targetRenewal, targetID*
tblProd2: prod2ID, targetNewBus, targetRenewal, targetID*
etc... until tblProd6
* to link with tblTarget and in case of update I could search by using the targetID

or should I try:
tblTarget: targetID, clientID, month, year, prod1ID, prod2ID,..., prod6ID
tblProd1: prod1ID, targetNewBus, targetRenewal
tblProd2: prod2ID, targetNewBus, targetRenewal
etc... until tblProd6

or even:
tblTarget: targetID, clientID, month, year, newBusID, RenewalID
tblNewBus: newBusID, prod1Target, prod2Target, ..., prod6Target
tblRenewal: renewalID, prod1Target, prod2Target, ..., prod6Target

I know I could probably have it all in 1 table like:
tblTarget: targetID, clientID, month, year, prod1NB, prod1Ren, prod2NB, prod2RN, etc...
but it would become a bit too big to handle, would you agree!?

Please, can I have your views on it (preferably explaining the decision so I can learn out of it)?

Many thanks for your help

mane_uk

 

[jdraw]

I suggest you start by reading this overview process of designing a database.
Then, work thru the process using your terms. Then pose specific questions to the forum.

http://www.databaseanswers.org/approach2db_design.htm

You may also want to look up normalization".
http://www.rogersaccesslibrary.com/forum/topic238.html

 

[mane_uk]

Hi Jdraw,

Thanks for the links but it is not much of a help at this stage. I have done all the step-by-step process mentioned in the site as well as the normalization and that is why I came up with 3 different table relationship approachs, my specific question would be which one of the approach mentioned in my previous question would work best based on your experience?

Thanks
mane_uk

 

[MarkK]

Hi mane_uk:
jdraw is gently telling you--obviously too gently--that none of your designs look good. None are normalized despite your claims.
I restate his suggestion that you need to do more research and/or ask different questions.
Cheers,
Mark

 

[mane_uk]

Hi Mark,
Sometimes is better to be straight to the point. It is easier to understand!! Thanks!!

OK, so, if I want it fully normalize it you would have the following
New business as tblNB: newbusID, newBusTarget
Renewal as tblRN: rnID, rnTarget
Products as tblProd: prodID, prodCode, prodDescription
Client as tblClient: clientID, clientCode, clientName
Date as tblDate: dateID, month, year
Main as tblMain: mainID, dateID, clientID, prodID, rnID, newbusID

If I want the following layout.

Client: dropdown combobox
month: textfield
year: textfield
<search button>
​

Prod 1 Prod 2 Prod 3 ....
RN NB RN NB RN NB
textfield textfield textfield textfield textfield textfield​

So user will enter a client from a drop down menu, enter month and year then click search if there is existing information the fields will be pre-populated otherwise it will be blank for new record.

So questions:
Case: User enter the information to be searched - I would have to first search tblDate for ID then cross reference with clientID and search the tblMain; that's ok!! So let's say result is null so they can add new case.
* First how can I know which Prod is about in terms that although my layout has an order as Prod1, Prod2, etc... my tblProd might have Prod4 as first item, then Prod6, then Prod1, etc... I don't know the order users will enter it when they populate the database and every different region will have 6 different products. How can I match the prodID with the label that I have in my form layout?
* How can I capture the ID just created for the renewal or new business to add it into my main table?
* As I have a front-end / back-end database I am doing everything in VBA, I would need to open 6 or 7 different recordsets to search all the information would not this make my application a lot slower?

Thanks
mane_uk

 

[jdraw]

1. You need a data model that reflects your business.

This model may give some ideas
http://www.databaseanswers.org/data_models/customers_and_products/index.htm

2. DO NOT get too involved too early with how queries and reports will work.
It is far more important to get your tables and relationships designed to meet your business.

 

[MarkK]

In your table design you don't need a tblDate. A Date is an atomic or scalar value and is always a dimension or measure of something else. Don't link to a date, simply put the date in the record where it belongs.
You don't need a tblMain. What is a Main? A row in a table represents a single instance of a thing. A Main is not a thing.
You have no foreign keys, or, none of your tables are related to each other. Consider these tables ...

tClient
ClientID (Primary Key)
ClientName

tProduct
ProductID (Primary Key)
ClientID (Foreign Key)
ProductName

See how one client can now have many products? Each product links back to a client on ClientID, so we've defined a one-to-many relationship. This is the essense of the power of a relational database, and someting that's just about impossible to model in Excel.
In your system, what is a renewal? What is a new business? Does it make any sense to have a tBusiness table that has an IsNew and an IsClient field ...?

tBusiness
BusinessID
BusinessName
IsClient
IsNew

See where this is going?
Cheers,
Mark

 

[mane_uk]

Thanks Mark and Thanks jdraw for your reply,

Mark I do understand where you coming from and my main application where I deal with clients and products bought/sold has this kind of approach but this issue I am having is to a paralell process specific built to gather the target needed to be reached by the client "re-selling" each product either through Renewal or New Business.

So, for example, my client A needs to sell 10 products 1 on Renewal approaches + 20 product 1 on New Business, my client B need to sell 4 product 2 on Renewal + 1 on New Business.

So, my NB and RN are not directly related to the client but it is a transaction that they are doing when re-selling our products; So as you can see I have many-to-many relationship all around. My clients can re-sell many products, products can have many clients; products will have many Renewal & New Business target (for each different client) and Renewal & New Business will have many different products; and so on...

So the approach of having 1 MAIN table was to unify it all. As mentioned in the links sent by jdraw, for you to have a many-to-many relationship you need a 3rd table to be able to "process" it; My MAIN table would be the "3rd table" for all the process unifying everything as they are all co-related!! Does it make sense?

Any thoughts please?

Thanks

 

[MarkK]

Obviously you're free to do whatever you think is best.
Cheers,
Mark

 

[mane_uk]

I know that I can do what I believe is best but then I would not come to a forum asking question, would I?

I do understand you are trying to help but I just can't see how it would work!! Give me some more details then on how would you tackle the problem that I have? How can you get all this many-to-many relationship put together? What would you tables be linked to one-another without making it huge?

Thanks