Design a database and write a SQL query for score application

user_1689520 0
Viewed 649

enter image description here

I am trying to design a database and write an SQL query for scoring devices based on certain criteria and options available.

Criteria here for example:

  • Hardware Strength
  • Software Security
  • Device OS

Options for each criteria are given in the table Master. Based on each option there is a score available.

I am trying to think of a SQL query using joins, but completely clueless as I am a JavaScript developer.

Is this even doable in a single SQL query?

sql algorithm database
3 Answers

Assuming you want the sum of the scores for the final score, you can use joins. Here is one method:

select d.*,
       (coalesce(mh.score, 0) + coalesce(ms.score, 0) + coalesce(md.score, 0)) as total_score
from data d left join
     master mh
     on mh.field = 'Hardware Strength' and
        mh.option = d.hardware_strength left join
     master ms
     on msfield = 'Sofware Security' and
        ms.option = d.software_security left join
     master md
     on mh.field = 'Device OS' and
        mh.option = d.device_os;
user_11440350

You can do it straightforwardly if you tidy up your database a little:

create table phones (
  id SERIAL PRIMARY KEY,
  model varchar(20) NOT NULL UNIQUE,
  hardware_strength INT NOT NULL,
  software_security INT NOT NULL,
  os INT NOT NULL
);

create table hardware_strength (
  id SERIAL PRIMARY KEY,
  description varchar(20) NOT NULL UNIQUE,
  score INT NOT NULL
);

create table software_security (
  id SERIAL PRIMARY KEY,
  description varchar(20) NOT NULL UNIQUE,
  score INT NOT NULL
);

create table os (
  id SERIAL PRIMARY KEY,
  description varchar(20) NOT NULL UNIQUE,
  score INT NOT NULL
);

with this model in place, the query is simple:

SELECT 
  a.*, 
  b.score + c.score + d.score as score
FROM phones a, hardware_strength b, software_security c, os d
WHERE 
  a.hardware_strength = b.id AND
  a.software_security = c.id AND
  a.os = d.id
;

You can play with it in this sqlfiddle.

user_8197200

The conventional way of setting up a structure with an arbitrary number of criteria would be something like the following:

create table phones (pid int identity primary key, pnam varchar(64));
insert into phones (pnam) values ('Google X'),('Samsung P');
create table props  (id int, crit varchar(32),
val varchar(32));
create table scores (critsc varchar(32), valsc varchar(32), score int);
insert into props values (1,'hw','metal'),(1,'sec','high'),(1,'os','android'), (2,'hw','diamond'),(2,'sec','low'),(2,'os','windows');
insert into scores values ('hw','plastic',3),('hw','glas',5),('hw','metal',8),('hw','diamond',10),('sec','low',2),('sec','high',5),('os','windows',4),('os','java',6),('os','meego',7),('os','android',10);

select coalesce(pnam,'ALL MODELS') pnam, 
       coalesce(crit,'total score') crit, 
       case when crit>'' then max(val) else '' end val, sum(score) score
from phones 
inner join props on pid=id
inner join scores on critsc=crit and valsc=val
group by pnam,crit with rollup

Output:

pnam        crit        val      score
Google X    hw          metal    8
Google X    os          android  10
Google X    sec         high     5
Google X    total score          23
Samsung P   hw          diamond  10
Samsung P   os          windows  4
Samsung P   sec         low      2
Samsung P   total score          16
ALL MODELS  total score          39

demo: https://rextester.com/NMKGY74929

Admittedly, this is not the shortest way of doing it, but it allows for all kinds of criteria-and-value schemes to be used without ever having to change the table structure.

user_26100610
Related