https://www.webmaster-forums.net/crss/node/1019232 en https://www.webmaster-forums.net/web-database-development/comment-book-store-db-design#comment-1114396 <blockquote class="bb-quote-body"><p>Quote: <em>Originally posted by Peter J. Boettcher </em><br /> [B]<br /> customerGroups Table<br /> --------------------<br /> Same except I would remove the customerGroupSponserID fk. You had two fks linked to each other, maybe I'm missing something.</p> <p>Then I would add a junction table.</p> <p>UsersCustomerGroups Table<br /> -------------------------<br /> UserID fk from users<br /> CustomerGroupID fk from customerGroups </p></blockquote> <p>The reason I did it this way is that I want each user to belong to no more than one customerGroup. The idea of a customerGroup is to set the products I display according to a user's group affiliation. The script will need to associate the user with only one group. Junction tables are for many to many relationships, which I don't want here.</p> <p>The same user, however, can be the sponser of lots of groups even though as a customer, he only belongs to one ConsumerGroup. That means he has helped me put together userGroups and may qualify for discounts based on how many books were bought by members of his group. It's sort of like an affiliate program.</p> Wed, 18 Sep 2002 17:45:12 +0000 akohl comment 1114396 at https://www.webmaster-forums.net https://www.webmaster-forums.net/web-database-development/comment-book-store-db-design#comment-1114316 <p>I'm not exactly sure how your app works but I think I would change it a bit. I would keep all your tables with the modifications below:</p> <p>users Table<br /> -----------<br /> Same except I would remove the customerGroupID fk</p> <p>customerGroups Table<br /> --------------------<br /> Same except I would remove the customerGroupSponserID fk. You had two fks linked to each other, maybe I'm missing something.</p> <p>Then I would add a junction table.</p> <p>UsersCustomerGroups Table<br /> -------------------------<br /> UserID fk from users<br /> CustomerGroupID fk from customerGroups</p> <p>I would also setup a separate Discount table</p> <p>Discounts Table<br /> ---------------<br /> DiscountID<br /> DiscountType<br /> DiscountDescription<br /> Any other fields you might need</p> <p>Then I would setup a junction table</p> <p>UsersDiscount Table<br /> -------------------<br /> UserID fk from users<br /> DiscountID fk from Discounts</p> <p>Also, from what I see hear it looks like you can't list what books every user has bought, only the customer group. You might want to add one more junction table.</p> <p>UsersBooks Table<br /> ----------------<br /> UserID fk from users<br /> BookID fk from books</p> <p>The reason I like to use junction tables is so I don't repeat data unnecessarily which makes the database larger and slows down your queries.</p> Wed, 18 Sep 2002 02:08:35 +0000 Peter J. Boettcher comment 1114316 at https://www.webmaster-forums.net