Forum Moderators: open
I sat down with my boss yesterday afternoon to run through how I was proposing to design the database. However, now I am more confused than ever.
He has been using Access for many years, and has questioned whether I will be able to produce reports from only using one column for the answer (ENUM). He feels from his experience with Access that each possible response (i.e. Very Satisfied, Fairly Satified, Fairly Unsatisfied, Very Unsatisfied), should have it's own column and numerical value(i.e. 100, 66.6, 33.3, 0).
This is so that the database can produce reports that show the average satisfaction nationally and for each retailer individually.
I would really appreciate some guidence, as I really don't want to get this wrong?
The second one stores the processed results of the first one.
So when a user opens a page the query was already done and stored in the DB rather having PHP doing the bulk of the job again and again
Hope it helps you
Your boss needs to realise that Access is not a database - it's a fancy front end to one which does various things that aren't the job of a database. Reporting is one of those. MySQL does no reporting or reports. That's your job - via PHP. So tell him he is right - MySQL won't be able to generate reports from a single enum column; but it won't be able to generate reports from his suggestion either as that's not the job of MySQL.
SELECT enumtype+0 AS enumtype FROM table will return numerical values.
SET types are similar but increment in powers of two, you can use bitwise operators for some different calculations on those.
...each possible response (i.e. Very Satisfied, Fairly Satified, Fairly Unsatisfied, Very Unsatisfied), should have it's own column and numerical value....
Perhaps you should demonstrate the following for him.
$levels = new Array ('Very Unsatisfied','Fairly Unsatisfied', 'Fairly Satisfied', 'Very Satisfied');
table ratings
id ¦ retailer_id ¦rating
1 ¦ 123 ¦ 1
2 ¦ 234 ¦ 2
3 ¦ 546 ¦ 3
So the above records would display as Fairly Unsatisfied ($levels[1],) Fairly Satisfied (levels[2],) and Very Satisfied ($levels[3]).
At any time, you could change the labels:
$levels = new Array ('Unsatisfied','Neutral', 'Satisfied', 'Ecstatic');
Oooh! We have a new level (represented by 4.)
$levels = new Array ('Very Unsatisfied','Fairly Unsatisfied', 'Fairly Satisfied', 'Very Satisfied', 'This site ROCKS!');
table ratings
id ¦ retailer_id ¦rating
23 ¦ 666 ¦ 4
You could store the rating column with the rest of the retailer data, but it might be more efficient in the report generation to not have to carry the whole row of the retailer data when generating a report.
