special_k423 Posted August 12, 2011 Share Posted August 12, 2011 This is what I have and I was wondering if someone could tell me what they think about it. Any advice is appreciated. Products pd_id manufacturer option (color or w/e) description specifications price sale_price distributor_1# distributor_2# distributor_3# manufacturer# inventory show_hide (viewable to public) stock_status (in stock/out of stock) pd_img_1 pd_img_2 pd_img_3 pd_img_4 pd_img_5 pd_img_6 pd_img_7 pd_img_8 weight date_added categories cat_id cname cmeta ctitle cimg menu (display in left hand navigation) manufacturer (manufacturer or category) subCategories sub_cat_id cat_id (parent id) sname smeta stitle simg productCat (relationship between product and it's multiple categories) cat_id sub_cat_id pd_id cart ct_id ct_session ct_qty ct_date pd_id orders order_id cart_id user_id sfirst_name slast_name ship_address ship_address2 ship_city ship_zip ship_state ship_country ship_amt (shipping cost) tax total date ship_status (shipped/ processing) comments (customer comments) notes (employee notes) users user_id email password bfirst_name blast_name bill_address bill_address2 bill_city bill_state bill_zip bill_country bill_phone cc_type cc_number cc_code cc_month cc_year user_ip Quote Link to comment https://forums.phpfreaks.com/topic/244646-advice-on-ecommerce-database-design/ Share on other sites More sharing options...
PHPiSean Posted August 12, 2011 Share Posted August 12, 2011 One thing I noticed as for the users is that they can spam registry information. I'd recommend generating a random string which is sent through email with a link in the form of a GET form to get around user registration. Quote Link to comment https://forums.phpfreaks.com/topic/244646-advice-on-ecommerce-database-design/#findComment-1256668 Share on other sites More sharing options...
thehippy Posted August 13, 2011 Share Posted August 13, 2011 Some background on the setup would be nice, which DBMS, physical location, if its a single storefront, local, international, multilingual, ties into external sources (inventory, CRM), etc. I'm not a fan of abbreviations in column names, pd* and ct* and hinted names such as cname and sname are useless as their full names are categories.cname and subCategories.sname which is a bit ridiculous, trim it up and make it cleaner. I think this is a preference but I know it annoys the hell out of real DBAs are table names should be named what a single row of the table is. A user record should come from the 'user' table, an order should be in the 'order' table. I say its up to you though. users can have many addresses (billing/home, work, friend/gift) then you can link to it (like in your orders table) users can have many payment methods (multiple credit cards, debit cards, payment gateways) some products have many options (ex. a shirt can have gender, size and colour) how are you dealing with that? make a table for all images, not a fan of n-number columns (ex. pd_img_n), less work if you need to edit image paths I also seperate out product and stock to allow the store owner to track stock a little more visibly. Stock can possibly be supplied by different distributors, its good to see where you're running low from who. Really depends on how much inventory management you want though. table for suppliers/distributors would be good and a m2m linking to which products they sell Storing credit card info is bad (a liability), avoid when you can. My country has privacy standards and as such credit card info needs to be encrypted and not in plain text it falls under 'reasonable protection'. PCI Compliance is becomming more important, check out the Data Security Standard (DSS), Payment Application Data Security Standard (PA-DSS) and PIN Transaction Security (PTS) documents. They cover a lot, but its always good to be aware. Put indexes on all your columns you sort by, if you can sort a result listing by price, weight, manufacturer, etc put an index on it. There are a couple of little things, column names are a bit inconsistent, you use 'description' and 'specifications' but later you have 'ct_qty' and 'ship_amt'. Some DBMS dont allow capitals in table names, might want to lower-case those. Taxes are a bit queer, they can be where the seller is, where the buyer is, there can be no tax and there can be customs/broker fees. Kind of depends where you are and where you're selling to. Have I mentioned I dislike dealing with them? password_md5 or password_sha1 may be more descriptive and appropriate I think that's all I have to say for now. Quote Link to comment https://forums.phpfreaks.com/topic/244646-advice-on-ecommerce-database-design/#findComment-1256759 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.