If you want to restrict your votes to "one user can vote for another user once", you simply use the two user id:s as primary key. As for the vote, just use one column and make it an enum:
CREATE TABLE IF NOT EXISTS `user_votes` (
`voter` int(11) NOT NULL,
`voted_for` int(11) NOT NULL,
`vote` enum('up','down') NOT NULL,
PRIMARY KEY (`voter`,`voted_for`)
)
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.
Best Answer
MySQL does not allow functions in keys, only column references. So if you want to restrict by a UNIQUE key, you need another column that would contain only the year and month for the event. The easiest and most straightforward approach is to reduce the
datevalue to something likeYYYY-MM-01. If you are using MySQL 5.7 or newer, you can declare that column as generated and define an expression converting the day part to the constant 01 value.And now you can create the
UNIQUE INDEX permonth (user, product, rdate). This is the uniqueness established on the physical level.On the logical level uniqueness can be achieved by (at least) two common ways.
First, you can define a
BEFORE INSERTtrigger that would perform extended comparison of the dates and decline to insert a duplicate. But triggers are not a very clean and easy tool because of their implicitness, and my advise is to avoid triggers if possible.The more robust way is to wrap the insert statement into a stored routine and perform
call SafeVoteInsert( userID, productID, date )instead of plainINSERT vote VALUES ....