Hi, I am working on a project that I am sure is going to test my skills, and force me to learn more, but I am looking for some help on the db tables. I am no stranger to php and mysql, but I am no expert by any stretch either. Things I have done just involved simple select, delete, insert type statements. With this project I am going to get in to joins and more complex stuff. I want to design the db from the start to be very expandable and as efficient as possible. First I am working on the client login and signup areas. I have done these types of scripts before, but with this one, I am making it so clients have different levels of usage of the site. this could be 3 different levels, or I could add more in the future. each level of membership could have a different payment option as well, like monthly, quarterly, annually. each level could have different pricing as well for different promotions, or coupon codes that can be used. So, this is more complex than I have done before. I have an ideal how to script the php part, but the table layout is what I am trying to decide on to make it work well. I would like your thoughts on the table layout . I want to make the db normalized for efficiency. client table: id, first_name, last_name, business_name, title, address1, address2, city, state, zip, country, phone, email, password, signup_date, signup_ip, email_confirmed, newsletter, last_login_date, last_login_ip, client_permissions_id The email will be unique and used for the login name. Should I seperate this table so the personal information is in a different table from the login information? like client_login table id, client_id, email, password, last_login_date, last_login_ip, client_permissions_id then the rest of the stuff in the client table? Certain levels of membership will also allow the main account holder to add additional users to their account. So, if I did the two different tables, I could use the main client's id and the additional user's email and password to log them in. The main account holder will also be able to decide what to give the additional user access to, like read only on some parts, and fully manage others, while having no access of even other areas. The only issue I see with this approach is I would want at least first and last name for the additional user, and if they happen to be spouses that share an email address. Would this kind of table layout work? client_login table id, client_id, additional_user_id, user_name, password, last_login_date, last_login_ip, client_permissions_id username would be unique, and additional_user_id be the id from the client_info table. If they were a main user the additional_user_id field would have 0, and if they are an additional user, the client_id would be the id for the client they are under. client_info table: id, first_name, last_name, business_name, title, address1, address2, city, state, zip, country, phone, email, signup_date, signup_ip, email_confirmed, newsletter, additional_user email would be unique in this table, the additional_user field would be 0 for no, and 1 for yes, and if they are an additional user, they would not receive any emails concerning the account, or newsletter. that is why I think I would need that extra column, to know who is an additional user or not. Email would also be optional for an additional user, but required for the main account holder. or one table for users like this client table: id, first_name, last_name, business_name, title, address1, address2, city, state, zip, country, phone, email, user_name, password, signup_date, signup_ip, email_confirmed, newsletter, last_login_date, last_login_ip, additional_user, parent_client_id, client_permissions_id thoughts??? Wow, this is getting long... Now for the different levels of membership. client_permissions table id, client_id, number_of_accounts, account_management, profile_management, additional_user_management, etc... This table will determan what the main user and additional user are able to do. I could use a client_id of zero for default levels that are mapped to the client levels for pricing. client_level table id, name, description, item_number, client_permissions_id, price, terms, show show would be if I wanted to display the level to the client when they signup. i figured I need this so if I update plans, I can still leave them in the table for users that are currently on those, but not show them to new users. I'll be using paypal's ipn for payments, and i figure I still need a way to have old plans for clients still on them. coupons id, code, start_date, expiration_date, type, amount type would be for either $ or % off coupons. valid_coupons id, coupon_id, client_level_id This table would map the coupon to the different client levels it is valid for. I don't want a $20 off coupon being used on a $5 level. Well, I think that brings me to the end. am I on track, or am I crazy? Any thoughts and suggestions are welcome. Thanks Michael
Wow, extensive project and should definitely test your skills. I'm fairly new to php, mysql. There is a script called xprotection that I use to control user access. It might give you some guidance on how to go about restricting certain pages for certain users etc.