G'day Kontos, These questions are to determine if the structure I have adopted would suit your needs.
Can the customer have more than one representative at any one time or would they have one rep for a period then another after then? When a rep makes a payment is it on behalf of a customer or on behalf of the rep? If it is on behalf of the rep, why? Does it later get assigned to a customer? What determines the relationship between the customer and the rep? I have a persons table which can store an introducing rep, a currently servicing rep and a last servicing rep. The payment header table has PersonID and the rep ID so can store any number of combinations. There is a Personnel table that stores rep IDs. If I wanted to go further I could have a table between Persons and Personnel that allowed for an unlimited combination and history. That may be what you need. CREATE TABLE `PersonsReps` + (`PersonID` INTEGER , + `RepID` INTEGER , + `Relationship` INTEGER , + `RelStartDate` DATE , + `RelEndDate` DATE ) At 09:51 16/04/02 +0300, you wrote: >I think your solution cannot apply here because the rule (of reality) is: >Payments can be done to _any combination_ of: customer and related >representatives > >For example: >customer CUST1 has related representatives REPR1,REPR2 >There could be 3 payments (one for each CUST1,REPR1,REPR2) >or 2 payments (REPR2,CUST1 for example) >or 1 payment (CUST1 for example) > >So what? > >I insist on this because I've met the same problem on many other situations. > >P. Kontos > > >Hi Kontos, > >You did not give enough information to advice you. > >Tables CUSTOMERS and REPRESENTATIVES have one-to-many connection by CUST_ID. >You should have CUST_ID column into REPRESENTATIVES table. You should have >at least one row into REPRESENTATIVES table for everyone customer. If you do >not have, just put some data from CUSTOMERS into REPRESENTATIVES for that >certain row. >Next step will be to connect PAYMENTS table with REPRESENTATIVES by REPR_ID >column. >This connection will be one-to-many. You do not need in PAYMENT(S) table >columns KIND_ID and WHO_ID! This technik is known as ?build an additional >table between two tables?. >Having representative for everyone customer will solve the problem in >accordance with first, second, and third normalization forms for these three >tables. > >Konstantin Gudjev > > > > >Table CUSTOMERS (CUST_ID,...), table REPRESENTATIVES (REPR_ID,....) with > >1:m > >(optional) relation > > > >so 1 customer may have none,1 or many representatives > > > >there is also table PAYMENTS > > > >now what is the best design according to normalization: > > > >1. PAYMENT (PAYMNT_ID,KIND_ID,WHO_ID,..) > >with KIND_ID = 'C' for CUSTOMERS, 'R' for REPRESENTATIVES) > >and WHO_ID = CUST_ID or REPR_ID respectively > > > >2. tables PAYCUST (PAYCUST_ID,CUST_ID,...) and PAYREPR > >(PAYREPR_ID,REPR_ID,..) for each group of data > > > >never managed to solve this > > > >TIA > > > >P.Kontos > >Athens, Greece > > >_________________________________________________________________ >Join the world?s largest e-mail service with MSN Hotmail. >http://www.hotmail.com > >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: INTRO rbase-l >================================================ >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: UNSUBSCRIBE rbase-l >================================================ >TO SEARCH ARCHIVES: >http://www.mail-archive.com/rbase-l%40sonetmail.com/ > >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: INTRO rbase-l >================================================ >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: UNSUBSCRIBE rbase-l >================================================ >TO SEARCH ARCHIVES: >http://www.mail-archive.com/rbase-l%40sonetmail.com/ Warmest regards, Tom Grimshaw coy: Just For You Software tel: 612 9552 3311 fax: 612 9566 2164 mobile: 0414 675 903 post: PO Box 470 Glebe NSW 2037 Australia street: 3/66 Wentworth Park Rd Glebe NSW 2037 email: [EMAIL PROTECTED] web: www.just4usoftware.com.au This email and any files transmitted with it are confidential to the intended recipient and may be privileged. If you have received this email inadvertently or you are not the intended recipient, you may not disseminate, distribute, copy or in any way rely on it. Further, you should notify the sender immediately and delete the email from your computer. Whilst we have taken precautions to alert us to the presence of computer viruses, we cannot guarantee that this email and any files transmitted with it are free from such viruses. ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
