You need to make a distinction between the possible answers and the selected answers.
The Option
table needs to be two tables. The Option
table should be 1:M to Question
and should include the possible answers for that question.
Then you need to make a new intersection entity, call it Selected_Option
which sits between User
and Option
.
If your question gives the user an opportunity to fill in a value as an answer (i.e. "OTHER:...") then this value would be stored in the Selected_Option
table. Otherwise the value chosen by the user would be the value found in Option
.
EDIT:
Based on OP's clarification of requirements: What you need is not like a typical questionnaire model in the following ways:
- Your questions all have the same sets of answers (columns)
- Some of your answers (columns) are grouped together.
- Blocks of questions are grouped together.
Taking your form snapshot as a guide, I've divided up the elements of your form into entities which I've colour coded:
This could be accomodated by the following logical ERD:
Note that I've colour coded the entities in the ERD to correspond to the snapshot of your sample form to show the correlation.
One of the assumptions in this model is that each block has only one set of quesitons (i.e. one QUESTION_GROUP
) which corresponds to the left-hand column in the block. This is a bit of a simplifying assumption.
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
You can have two entities
products_store
andproducts_stock
as depicted in your question.Other Options
I think of having single
Products
table with a column name it asIn_stock
(can be abit
field) where you change it to false, when the product is moved to store.In_stock
value options1
- InStock0
- InStoreI think of having single
Products
table with one column calledMove_DateTime
(default valueNULL
) to update the column when the product is moved to store from stock.Move_DateTime
value optionsNULL
- InStockDateTimevalue
-InStoreIn one possible scenario you can go for choosing both the columns to be added in the table.