Mysql DB structure (architecture) advice

Question

Check this image of a table - http://puu.sh/3ipDq.png

How can I model this in MySQL (sql) ? I want to be able to store data contained in this table in a normalized fashion in my database table. I should be able to query the DB easily and form this exact table on the webpage (using HTML).

The data should be stored well enough to be able to do some computation (aggregation functions), filters, comparisons, search, etc. on such data sets (assuming after some time we'll have big amount of data, so basically queries should be performant).

If that makes sense, some help would be appreciated. I've tried modelling this data using some key/val structure in mind but without success. Hard to maintain relationship between rows/cols. For example, the first row in second column is related to first row in 3rd, 4th, nth column.

Another solution is to make a table with as many columns as shown in the image. But the problem here is, this is just a sample table. There's going to be more tables like these with variable columns, complete un-related column name/type, value, etc. For example here's another table - http://puu.sh/3iq0g.png - I want to be able to store data from both tables (and ofcourse many more related and unrelated tables) in a "generic" manner. First thought was key/val method but I failed.

Should I maybe consider using some NoSQL solution for this part?

Answer 1

Since your data isn't sorted in alphabetical order, you'll need to include tables that let you control the sort order. You'll need one to control the sort order for cabs.

create table cab_sort_order (
  cab_type varchar(35) not null,
  cab_type_sort_order integer not null,
  primary key (cab_type)
  );

insert into cab_sort_order values
('Toyota Etios', 1),
('Maruti Suzuki - SX4', 2),
('Airport Media', 3),
('Cab Meter Receipts', 4);

And one to control the sort order for media options.

create table media_option_sort_order (
  media_option varchar(35) not null,
  media_option_sort_order integer not null,
  primary key (media_option)
  );

insert into media_option_sort_order values 
('Cab Exterior', 1),
('Sampling', 2),
('Leaflets / Flat & Sun Visor Cover', 3),
('Seat Flap & Sun Visor Cover', 4),
('Rooftop Carrier', 5),
('Raoad Show - 10 Cabs', 6),
('Boarding Pass', 1);

Both of those tables are in 5NF.

The structure of cab_media_options is straightforward.

create table cab_media_options (
  cab_type varchar(35) not null,
  media_option varchar(35) not null,
  placement varchar(35) null,
  price integer not null,
  unit varchar(35) not null,
  primary key (cab_type, media_option),
  foreign key (cab_type) references cab_sort_order (cab_type),
  foreign key (media_option) references media_option_sort_order (media_option)
  );

insert into cab_media_options values
('Toyota Etios', 'Cab exterior', '4 doors + boot', 11000, 'Cab / Month'),
('Toyota Etios', 'Sampling', NULL, 2500, 'Cab / Month'),
('Toyota Etios', 'Leaflets / Flat & Sun Visor Cover', NULL, 2000, 'Cab / Month'),
('Maruti Suzuki - SX4', 'Cab exterior', '4 doors + boot', 12000, 'Cab / Month'),
('Maruti Suzuki - SX4', 'Sampling', NULL, 3000, 'Cab / Month'),
('Airport Media', 'Boarding Pass', 'Back Side of Pass', 600000, 'Month / All Cabs');

I don't know what all your columns mean, but that table is probably in 5NF, too.

To get the data you need to match your linked image, join cab_media_options to the two tables that control the sort order.

select cmo.*, cs.cab_type_sort_order, ms.media_option_sort_order
from cab_media_options cmo
inner join cab_sort_order cs
        on cmo.cab_type = cs.cab_type
inner join media_option_sort_order ms
on cmo.media_option = ms.media_option
order by cs.cab_type_sort_order, ms.media_option_sort_order

You can increase data integrity with additional tables. For example, you can create a table of cab types that has a single column.

Table: cab_types
cab_type
--
Toyota Etios
Maruti Suzuki - SX4
Airport Media
Cab Meter Receipts

Then set a foreign key in cab_sort_order to reference cab_types, and (probably) replace the foreign key in cab_media_options with a foreign key referencing cab_types.

Increasing data integrity this way has nothing to do with normalization. (A lot of design decisions have nothing to do with normalization.)

Answer 2

This is not complex.

You need several tables though:

Cab_type       PK cabtype     details...
Media_option   PK mediatype   details...
Placement      PK placement   details...
Unit           PK unit        details...
pricing        PK (serial number)  FK cabtype FK mediatype FK placement FK unit  Price

I believe this would fit all definitions of a normal form database and has the advantage of being a doddle to query to get the information you need as any table can be linked via the pricing table.

Hope this helps.

Vis your second table and your envisaged n tables, this is where normal forms come into their own. You do at some point though have to code the relationships in. If for instance, unit was ALWAYS associated with placement for one purpose, you could create a table unit_placement which includes the FK from each table to DESCRIBE the relationships. You do eventually have to enter some data that links things together. It doesn't happen automatically (very often!)