This is my typical migrations for a e-commerce site (small one)
Schema::create('categories', function(Blueprint $table)
{
$table->increments('id');
$table->string('name', 60)->unique();
$table->string('description');
$table->boolean('show_description')->default(0) // default do not show
$table->integer('order')->default(1) // Default for testing and seeding, need logic to ensure the order is set to next number
$table->timestamps();
});
Schema::create('products', function(Blueprint $table) // Items, or what ever you want to call the table
{
$table->increments('id');
$table->integer('category_id'); // if you want many categories to many products, use a pivot table and hasManyThrough in each model
$table->string('name')->unique();
$table->string('over_view'); // Small description
$table->text('full_description'); // full blown description
$table->text('specifications'); // Optional, and can continue adding as required
$table->integer('order')->default(1) // Default for testing and seeding, need logic to ensure the order is set to next number
$table->dateTime('sell_at'); // So you can pre-load products before the sell date
$table->timestamps();
});
Schema::create('prices', function(Blueprint $table)
{
$table->increments('id');
$table->integer('product_id');
$table->decimal('base_price', 5, 2); // This will allow prices up to 99999.99 (5 numbers left of decimal, 2 after)
$table->decimal('sale_price', 5, 2); // set a sale price if or when an item goes on sale
$table->dateTime('sale_start');
$table->dateTime('sale_end');
$table->timestamps();
});
Schema::create('product_information', function(Blueprint $table)
{
$table->increments('id');
$table->integer('product_id');
$table->decimal('cost', 5, 2); // cost per unit
$table->integer('in_stock'); // running tally of amount in stock
$table->integer('low_stock_alert')->default(0); // set unit stock amount to trigger notification to reorder, default 0 so null space isn't used if not required
$table->boolean('stock_alert')->default(0); // set alerts on or off, default off
$table->timestamps();
});
this is a quick copy and past. Well I have more tables holding reports and so on to show margins, stock cost, profitability, units sold per month etc.
But this is the basic set up I start with and scale it as required. I use a separate pricing table because many times it runs on a pivot table with many to many as re-ordering a unit might cost more and when the less expensive stock runs out the client will automatically be able to raise the price when the more expensive unit is up for sale (if that makes sense).
