critique my online bookstore db design

[avikohl]

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 properties of discounts which can be used for individuals or groups.

As I write this question the issues are already becoming a bit more clear to me. But I culd still use some input.


Suggestions 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
publishDate


pageVisits
----------
pageVisitID
bookID
userID
IP
browser
visitDateEntered

 

[MichaelRed]

At a glance, you are courting CERTAIN disaster. It is clear that you should NEVER store credit card information with persistant records. How many headlines have there been re hackers finding this in some company's data base and using it to create trouble. There are companies which specialize in processing (on-line) credit card payments. You send them the information (they provide the software -which includes encryption) and they take responsability for the verification and payment processing.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[avikohl]

Thanks for your comments. I am aware of the problem of storing credit cards on a website db and have a plan to protect the privacy of my customers. This isn't really what I meant by critique of the db design. But while we are on the topic, can you recommend a company for processing online credit card payments that can service a buisiness outside the US?

 

[MichaelRed]

There is one 'example' - Duwamish books - somewhere in Ms. A. Land. I have not used it - but one programmer I have worked with says it provides only the minimum 'Demo' type of db. It might be useful as a sanity check. If you do not have at least what they have included, it must 'not be sufficient'?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[avikohl]

Just took a look at some marerial on the Duamish project at the msdn library. I couldn't find was most interested in; a diagram of the db tables and relationships. But I will be taking a look at the material there... Thanks for pointing me there. It looks quite interesting.

Being originaly from the Pacific Northwest, I think I've acually BEEN to Duamish, not that that's going to help me much...

 

[skiflyer]

Writing off the cuff here and not seriously thinking out any short comings to my logic...

I would forget about the if this field is null idea in your address table, and go ahead and abstract that other info all by itself, then have another table which is just recipients.

Then the two more many to many tables that pop up to store basically

Customer ID <--> Recip ID

and

Customer/Recip ID <--> Address

-Rob