How to Structure Renting and Sale as Type of Purchase in MySQL

database-designMySQL

I'm currently planning a database which involves two different types of Products that can be bought outright, or rented for a weekly or monthly amount. I'm just trying to figure out how to structure the database for this.

I'd like to keep the Products all in the same table. The only way I've thought of so far is to have my Product table reference another ProductType table which has rental or sale (not sure on the wording here really), then have another table which is linked to the product which has the cost. I don't think this is correct however.

How can I structure this relationship? (I don't mind any changes to this at all, other than the Products all being listed in that one table still).

Best Answer

As long as you don't have any additional data to store with the "rental" or "sell" property, you don't need a table for it. Just use a COLUMN manage ENUM('rent', 'sale'). Then you have to think about prices. The "BuyMe" price seems simple. Renting Models may have a real interesting live, I mean there are a lot of ideas which come in mind, and much more ideas which already live out there, not to speak about the infinite and strange wishes of some Marketing Stuff. So you would be wise enough to point the product to a category of price models. When you have rental, I assume that some products are more or less equal.

This Price Category can then be resolved to a Price Model, so you can combine the time of rental together with the Price Model to the real price.

In the beginning start with your original idea of just a price per day (or price per hour), the Price Model contains just this single one value and you multiply number of days (or of hours) with that single number. When it gets more complicated, just extend the table (and the code, of course).

So you have a table:

CREATE TABLE products (
      id int not null auto_increment primary key
    /* , name, praising, weight, properties... */
    , manage ENUM('rent', 'sale')
    , pricecategory_id int DEFAULT NULL
    , sale_price decimal(12,2) DEFAULT NULL
    /* indices */
);

CREATE TABLE pricecategory (
      id int not null auto_increment primary key
    , name varchar(255)
);

CREATE TABLE pricemodel (
      id int not null auto_increment primary key
    , name varchar(255)
    , pricecategory_id int not null
    , price_per_day decimal(18,5)
    , index(pricecategory_id)
);

You refer from pricemodel to pricecategory, since sooner or later you might get different types of customers, which get different prices (Gold Card Members or something like that). So you just add the customertype to pricemodel and are ready for the beginning.

Think about Special Offers, Easter Bundles etc. products.sale_price is only set for the sale case, and pricecategory_id only for rent.

Have fun with the application and grow! I know of Price Models which tend to need nice Graph Theory Optimization to get calculated, since the different paths have to be compared and of cause the customer gets 'best price' between hour, day, and a lot more.

Related Question