ER Problems, limiting a MANY TO MANY

Results 1 to 5 of 5

Thread: ER Problems, limiting a MANY TO MANY

  1. #1

    Default ER Problems, limiting a MANY TO MANY

    I just came across a situation where I need to limit the number in a many to many relationship. I have customers and products (MM). No big deal right. I also want to limit how many they can buy from each section of the store. A customer can buy many products from the store, but is limited to one per section. <BR><BR>So lets say joe wants to buy some beer. We offer many different brands, but he can only choose one. The same goes for each section such as chips, fruit, wine, etc.<BR><BR>My first hack was to just put fields such as beer, wines, etc. onto joes info and create a one to many with the products. That of course is a serious hack and may work up until the point you actually tried to use it.<BR><BR>Maybe I am just looking at this wrong or to deep. Any help would be great.<BR>Thanks,<BR>DRUG_DEALER

  2. #2
    Join Date
    Dec 1969

    Default RE: ER Problems, limiting a MANY TO MANY

    drug dealer-<BR><BR>you need to create an additional table that will hold the id of the person, along with the id of what the person has all bought. so all in all, in 3NF, you would have 3 tables to represent this, like:<BR><BR>table1_person (PK id field and desc of person field)<BR>-------------<BR>1 joe<BR><BR>table2_person_alcohol (FK fields referencing the ID fields)<BR>---------------------<BR>1 35<BR>1 45<BR><BR><BR>table3_the_alcohol_list (PK id, and desc fields)<BR>-------------------<BR>35 miller<BR>45 bud<BR>50 wine<BR><BR><BR>hope that helps.

  3. #3

    Default OK, I understand that, But I need to limit it..

    I understand that part of the many to many. What I am having problems with is I need to limit the selection for each type. Something like whats in your example, but they are only limited to buying one type of "beer". So they have to choose, do they want bud or miller? I think maybe I need another table called "types" and a junction table between types and users. Then using the PK from types and the PK from users to form the PK for the junction table. This will keep the user from buying more then one type of beer. The problem is I dont see where it fits into my ER and still get 3NF out of it. I hope this makes sense.<BR><BR>I really do appricate any help.<BR>thanks,<BR>drug_Dealer

  4. #4
    Join Date
    Dec 1969

    Default RE: OK, I understand that, But I need to limit it.

    hiya druggy :-)<BR><BR>here&#039s an idea...<BR><BR>You still need the table between customer and product, lets call it &#039purchase&#039.<BR><BR>I assume on your product table you have a column which stores the section code as well as the item code eg: &#039Beer&#039, &#039Bud&#039 ... not that I like American beer but I digress :-)<BR><BR>so make the middle table like this:<BR><BR>cust_id, prod_id, section_id<BR><BR>Make a unique constraint (index) on cust_id and section_id in this table. <BR><BR>Now whenever you try to insert/update records in the &#039purchase&#039 table, if a record is there for the same combination of customer and section, then the operation will fail. You can trap this error and give an appropriate response to user eg: "You can only have 1 Beer.<BR><BR>(but who on earth would shop at a place you can only have *ONE* beer???? ;-)<BR><BR>Note that this is a denormalised design, defintely not 3NF. It is one of those cases where you bend the rules to satisfy a unique requirement. <BR><BR>last word: always be careful when you denormalise like this.. think about how big tables will be and how much redundant data you have, and trade off against what you will gain out of it.<BR><BR>good luck<BR><BR>

  5. #5

    Default I think that just may work...

    I am a little worried about traffic. Of course I am hoping the server will get slamed and we all get rich (its actually game I am building). I am going to do a quick work up and throw a little stress test at it. <BR><BR>I owe ya a beer, as long as its not any of that import stuff us students cannot afford :)<BR><BR>Drug_dealer<BR>(ZIMA drinker)<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts