Level 4
Sorry, the link was broken. fixed now.
Sep 30, 2015
4
Level 4
Database design for Ecommerce
Before asking my quetions, I'm refering to this great website - http://www.databaseanswers.org/data_models to find answers. Take a look and you will learn a lot of stuff from here :)
Okay, I'm designing a database schema for an ecommerce website. This website will display all the available online courses it offers and people can either sign up and pay online to make a reservation or just visit its office to do so - paying with credit card or cash.
The following is a part of database schema I've designed.
==
User Table: id(PK) / email / password / ...
==
Course Table: id(PK) / course / ...
==
Payment Table: id(PK) / amount / tax / ...
==
UserCourse: user_id (FK)/ course_id (FK)/ payment_id (FK)/ ...
==
Well, it looks good - a simple many-to-many relationship. However, this assumes that all users have their own accounts. My confusion kicks in here.
-
The user table stores information of registered users. Should I store walk-in customers data here too even if they technically are not registered?
-
If yes, what if that 'walk-in' user wants to create his account? Should he use the temporarily created user account by a staff? or Should I let him create his own one? - Well... I also have multiple number of gmail accounts... so I guess yes to this question...
-
Is it a good idea to create user_id field in Payment table? I've refered to some examples on http://www.databaseanswers.org/data_models Some people do and some people don't... Not quite sure which one to go with :S
Thanks !!
Level 36
The user table stores information of registered users. Should I store walk-in customers data here too even if they technically are not registered?
imho no, you shouldn't. These data is store in a session and when the user checks out, session will be deleted. You can offer the user to register before he checks out and let him know the benifits of a registration.
Level 4
@frezno Thank you for your reply. I also thought about that option... but still don't know what to do if the client says 'Not interested.' Another option I came up with is to create 'is_temporary_user' field in the user table. If a user tries to sign up with temporarily registered email, then his information will overwrite whatever was previously stored in the table about the user.... I cannot say this is the best way but seems not that bad...
Level 36
i don't exactly understand your problem right now.
If the user doesn't want to get registered, all data is stored in a session.
You have to use a session anyways, in case the user choses some courses and logs in after he has done his shopping.
That said, you have all user data in a session. If he registers/logs in, write those data to the appropriate tables when checking out.
If he doesn't want to get registered or doesn't logs in, you have four choices:
- Since you very likely mail the user a confirmation of his purchase, mail a copy of it to you as well and that way you have all the necessary data.
Drawback, if the mail gets lost, the data is lost as well - Write the data to a flat file on you server (mails to user and you will be sent as well)
- Write the data to a separate table of your db, which is used just for not registered users (+ mails, as mentioned above)
- Treat this user the same as a registered user (using all the same tables as for the reg. users) but flag the user as guest. That might involve altering your existing users table since there won't be any login, pw etc data as well.
I personally don't like this variant though
But as mentioned before, all data is stored in a session and only when checking out this (session) data is handled the way you want.
Please or to participate in this conversation.