Two appraoches in table design

[arishy]

Tables client and product. It is many to many

1. Approach I
Unique Client table and Unique Product table and Junction Client/Product table

2. Approach II
Client with product FK, and Product with Client FK and the junction table.

The nature of the project is; Clients will grow, but products rarely grow(much slower) We are talking 30->200 for clients and 10->25 for products.

Also, the reporting is basically to answer: what Clients using what products. And vice versa. No transactions( i.e. sales) involved here .

My concern is Normalization in the second approach.

Also, I have no idea how to create data entry forms in such a situation(s).

 

[pr2-eugin]

arishy, is there a Reason why you have the idea of Many-Many relationship here?

Normally One Client will use Many Products.. Of course there will be many Clients in the Client table.. Your structure should be..

tblClients
clientID - PK
clientName
clientOtherInfo

tblProducts
prodID - PK
prodDescription

tblProductUsage
usageID - PK
clientID_FK - FK
prodID_FK - FK
usageOtherInfo

Sample Data:

[U][B]tblClient[/B][/U]
clientID    clientName    clientOtherInfo
1            Paul         Bournemouth
2            Leo          Bristol

[U][B]tblProducts[/B][/U]
prodID    prodDescription
1         Printer
2         Scanner
3         Laptop

[U][B]tblProductUsage[/B][/U]
usageID    clientID_FK  prodID_FK    usageOtherInfo
1            1          1            15/05/2013
2            1          3            01/01/2010
3            2          2            07/08/2013

 

[arishy]

A product can be used by many Clients, As A client can use many products.

So, you selected Approach I.

In this case I have to create a composite primary key ( Client PK + Product PK) to avoid duplicates. Right ??

I am still hoping that some one "shoots" Approach II and tell me why !!!!

Any Idea how to data entry the Junction Table? ( you called it usage)

 

[Pat Hartman]

Approach II creates a pathological relationship. If you enforce RI, you won't be able to add a client because the product doesn't exist and you won't be able to add a product because the client doesn't exist.

Approach I is correct and Approach II is not.

I posted a many-to-many sample in the samples section that you can examine. It uses two main forms and two subforms to show the relationships from either perspective.

 

[arishy]

Pat, you came to my rescue....again...