comment on this book store db design
Here are the tables that store information about website users, mainly customers, their accounts, login keys, group affiliations, discounts, primary addresses and shipping addresses.
One thing I wasn't sure about was whether or not to store discount information in the users table in addition to the customerGroups table as I have done here. An individual user may have his own discount details. Lets say we want to compensate him
for a late delivery by giving him a 15% discount for one month. Or should I make another table, "discounts" where I define discount properties which can be used for individuals or groups?
As I write this question the issues are already becoming a bit more clear to me.
Suggestions and criticism from all angles welcome!
users
--------------
userID pk
userNameFirst
userNameMiddle
userNameLast
userEmail
userPassword
usePhoneAreaCode
userPhone
userPhoneCell
administrator
userDiscountPCT
userDiscountStartDate
userDiscountExpireDate
customerGroupID fk
accounts
--------------
accountID pk
userID fk
CCType
CCNumber
CCDateExpires
accountDateEntered
valid
addresses
--------------
addressID pk
recipientFullName where null, listing represents a primary address, the one given
along with credit card account information. For this Address,
the name was written to the name fields in the related users field.
pob
street
city
state
zip
country
userID fk
customerGroups
--------------
customerGroupID pk
customerGroupName
customerGroupDescription
CGDiscountPCT
CGDiscountStartDate
CGDiscountExpireDate
customerGroupSponserID fk from userID
books/cutomerGroups
--------------
bookID
customerGroupID
books
--------------
bookID pk
bookTitle
bookSubtitle
bookDescription
bookImagefile
publisherId fk
publishDateAndy Kohlenberg
Jerusalem, Israel
Peter J. Boettcher posted this at 02:08 — 18th September 2002.
They have: 812 posts
Joined: Feb 2000
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:
users Table
-----------
Same except I would remove the customerGroupID fk
customerGroups Table
--------------------
Same except I would remove the customerGroupSponserID fk. You had two fks linked to each other, maybe I'm missing something.
Then I would add a junction table.
UsersCustomerGroups Table
-------------------------
UserID fk from users
CustomerGroupID fk from customerGroups
I would also setup a separate Discount table
Discounts Table
---------------
DiscountID
DiscountType
DiscountDescription
Any other fields you might need
Then I would setup a junction table
UsersDiscount Table
-------------------
UserID fk from users
DiscountID fk from Discounts
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.
UsersBooks Table
----------------
UserID fk from users
BookID fk from books
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.
PJ | Are we there yet?
pjboettcher.com
akohl posted this at 17:45 — 18th September 2002.
They have: 117 posts
Joined: Feb 2001
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.
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.
Andy Kohlenberg
Jerusalem, Israel
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.