TL;DR
Let's remember - Magento wasn't built for this. But is it possible? Sure.
The type of functionality that you describe is called a Marketplace and there are a couple of plugin providers who have offerings at differing levels of integration.
While this is entirely possible within the realm of Magento without you having to offer admin access up to scores of untrusted individuals it seems that (at the time of this writing) there is no freely-available means of configuring Magento to allow multiple vendors to sell inventory of a single, canonical, product. This will require you to purchase a 3rd-party offering and all of the potential upsides and downsides that come with it.
To that end if you're not comfortable with such a proposal I would recommend that you look hard at eBay and Amazon's marketplace offerings. There are SaaS providers, too, that span across all sorts of technologies.
You're still here? Good.
If you're still interested in using Magento for this type of functionality, I do have a couple of suggestions for you.
I have no personal experience implementing either of these options but have done extensive discovery and have talked with both of the developers and teams from Unirgy and Merchantry.
Unirgy uMarketplace:
http://www.unirgy.com/products/umarketplace/magento-multi-vendor-marketplace
Pros:
- Fixed pricing. No sales team and quotes and haggling.
- Magento Plugin, vendors register plugins to your site and are listed in Magento Catalog
- Top-notch developer and extremely helpful support
- Features are spot-on with your requirements and even go so far as to offer CSV upload to merchants
- Provides merchant portal, shipping label generation, sales reports
- Great upsell features like allowing multi-condition products (new/used) to be sold one-off
- Admin features to merge product offerings and moderate product posts prior to going live
Cons:
- Some classes have IonCube encoding
Some may bawk and say it's expensive; I say it's cheap for the functionality, IMHO, and if you run this type of Marketplace site and require these types of features I would hope you have the revenue to justify this type of investment.
Merchantry
http://www.merchantry.com/
Pros:
- SaaS solution used by major players (think Amazon)
- Built for scale. Hundreds of thousands, millions of products
- Massive API
Cons:
- No fixed pricing. Sales team and estimates and haggling and lawyers (oh my)
- VC funded (series B May 2013)
Without knowing exactly what your extension will do it's hard to say which direction I should recommend you go. But, generally, iI believe that it is best practice to extend and add columns to sales_flat_*
tables, as evidenced by Magento's own extensions that they have implemented.
For instance, in Enterprise Edition instead of creating join tables for sales data on Enterprise_GiftWrapping, for instance, they extend sales_flat_order
and sales_flat_order_item
to include relevant gw_*
fields to contain sales information as needed.
In my opinion there is little to no danger in making your edits to these tables on a 1.x install of Magento - unless, of course, you're wanting to support legacy Magento versions in which case sales data was EAV until ~1.4CE.
A potential argument against using join tables is that any join in the admin that keys against a flat table used for order placement (such as sales_flat_order
) has the potential of affecting a lock upon that table which may cause unintended problems for high-volume stores.
I had an admin sort by customer_email in the order grid view (a custom extension) - the email address is unindexed and the sort was on > 300k rows. This caused 3 orders to fail with a lock wait timeout, but payments had been made and authorized in the gateway. What's worse is that the customers were then unable to complete their order subsequently due to duplicate transaction id numbers, a limitation of the gateway.
So, proceed with caution... but in my opinion you're clear to edit the core tables.
Best Answer
Check out the Unirgy Dropship module suite - http://www.unirgy.com/products/udropship/. It has a lot of feature for working with vendors, including defining them & granting access to an interface where they can change info about their products.