Firstly this site is based on guests who are interested in selling their items to the site owner, so this isn't auction based like Ebay.
Visitors basically would enter in details of an item ( tech gadget ) they'd like to sell, including condition, are given a quote on how much the item is worth, then fill in address info so the owner ships them a box/label so they can ship their item to the owner. If the owner is content with the condition, the visitor gets paid either by check or Paypal.
So going by this, I would need to have some sort of orders
( or is it more semantically correct to call it a transaction, or something else? ) table in which I track every single 'order' that the customer makes.
The items accepted are preselected, so there are only a few specific models/manufacturers so I was going to make an items
table along with manufacturers and populate that like so:
items table:
item_id item_name manufacturer item_category_type
1 iPhone 32gb 3GS 1 1
2 Sidekick 3 2 1
manufacturers table:
id manufacturer_name
1 Apple
2 T-Mobile
There are also multiple types of items, item categories:
item_categories:
id item_category
1 phone
2 ipod
3 computer (pc,laptop)
orders:
id user_id item_id estimated_price ac_adapter battery manuals cables
1 5 2 250 1 1 1 1
users:
id user_email user_address user_city user_state user_zip user_country
1 [email protected] 302 Kingston Ave Manhattan New York 11111 United States
The price will be generated based on conditions, so for example whether or not a phone has an ac adapter, battery, manuals, cables, I indicated those in the orders
table but I'm not quite sure if I should even store that in a table.
Question 1: Is the way I'm storing the ac_adapter, battery, manuals column in the orders table the correct way of doing this? I'd appreciate any tips.
The site will also have an admin area where the admin can look at all the orders, and there will be a user area where the user can look at his orders, in addition theres a request to have emails stored there too.
So after a user makes an 'order' then I'll have to add an entry to the users
table if the user isn't already registered, and add an entry to the orders
table, along with emailing the user a copy of this and emailing the site owner a copy of the order information. And next comes the part I'm confused about, storing emails - is it as simple as just creating an emails table, and possibly a second table relating the order transactions with the emails? And just mail the email_body to both the site owner and the user? Here's the schema I'm thinking of:
emails table:
id order_number email_title email_body ( TEXT column? )
1 1 sitename.com - Order #1 ( stuff )
Question #2: And for the email body, the way I'd generate that is join all the columns dictated by the order number, and just make an HTML table out of all that, include the date.. is this usually how email storing in databases is done?
I apologize in advance as this is a very loaded question, hoping someone with enough experience in these types of sites can assist. Thanks.