Multiple columns in a row? MySQL question..

Movax

The topic may not make much sense, but I'll try to explain.

I'm guessing there is an easy way to do this properly, but I'm somewhat new to Mysql, and database design.

I am trying to replicate what is currently done on paper, but keep it as close to possible to shortcuts that are taken.

The paperwork is for recording volume of a proccessor (let's pretend beer). I need to record start time, end time, volume and destination tank, among other things. The destination tank is the problem.

They way things are done now, one row on paper normally refers to one dest. tank during one run. But one run can have mulitple destinations, with volumes attached. How do I represent this in a database? Example:

Start   End    Volume  Tank
0600   0700  10000    1

No problem, but what about:

Start   End    Volume  Tank
0600   0700  10000    1
----   ----    5000     2 
----   ----    2000     3

I have some ides, but I felt I should ask the best way to represent this.

Jack_McSlay

depends.
is start and end representing hours?
is there more information, valid for all data on each paper (such as date, location)?
what is the maximum volume possible?
how is the destination identified?

Movax

Yes, start and finish are times. There is other information in the row as well, such as flavor, volume of raw ingredients used. And yes, there is a date for each page. The destination is normally written in a way that would be represented by a string, but it would be no problem to recored it numerically. Maximum volume, to be safe should be at least 200 000.

The destinations and volume for each is the only thing that can have multiple entreis as I described.

HalfaBee

You need a run_id to tie the multiple rows together.
Then you can add as many destinations as you need.

To show data
SELECT * from table_name WHERE run_id = 123

Movax

Are you saying I should have a separate table with run_id (as index), destination, volume?

HalfaBee

I don't think you need a separate table for the id.
Just create the table with an extra column run_id.
run_id Start End Volume Tank

So run_id remains the same for the different destinations, I suppose if the times are left blank, this could indicate a different tank from the first one.

Jack_McSlay

from what I see, the ideal method to make the table is to store Start and End as timestamps on the database, and make it so that each row has it unique start and tank number (because you can't use a tank for two things at the same time), also storing volume. wether or not to store flavor and raw ingredients depends if it's relevant to the system you're doing. this table will need an auto-increment primary key as well.

then you'll need another table to identify the destination, containing a destination Id and a run id.

so my suggestion is:

CREATE TABLE IF NOT EXISTS `destination` (
  `dt_id` smallint(5) unsigned NOT NULL,
  `dt_serial` int(11) NOT NULL,
  `dt_name` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`dt_id`),
  UNIQUE KEY `dt_serial` (`dt_serial`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tankRun` (
  `tr_id` mediumint(8) unsigned NOT NULL auto_increment,
  `tr_tank` smallint(5) unsigned NOT NULL,
  `tr_start` timestamp NOT NULL default '0000-00-00 00:00:00',
  `tr_end` timestamp NOT NULL default '0000-00-00 00:00:00',
  `tr_volume` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY  (`tr_id`),
  UNIQUE KEY `tr_tank` (`tr_tank`,`tr_start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tankRunDest` (
  `trd_id` mediumint(8) unsigned NOT NULL,
  `trd_tankRunId` mediumint(8) unsigned NOT NULL,
  `trd_destinationId` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`trd_id`),
  UNIQUE KEY `trd_tankRunId` (`trd_tankRunId`,`trd_destinationId`),
  KEY `trd_destinationId` (`trd_destinationId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tankRunDest`
  ADD CONSTRAINT `tankRunDest_ibfk_2` FOREIGN KEY (`trd_destinationId`) REFERENCES `destination` (`dt_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `tankRunDest_ibfk_1` FOREIGN KEY (`trd_tankRunId`) REFERENCES `tankRun` (`tr_id`) ON UPDATE CASCADE;

(copy and paste this on the SQL function of phpmyadmin)

Movax

I was thinking of going with HalfaBee's idea. I considered this before, but didn't think this was the 'correct' way to do things, since it will waste space. I want to do things correctly from the start. Is this an okay method? (It's easy to understand 🙂 )

Jack McSlay wrote:
..make it so that each row has it unique start and tank number (because you can't use a tank for two things at the same time), also storing volume.

Thanks so far for the suggestions. I need some clarification on this point. Volume and tank need to be paired together. Your idea doesn't seem to be doing that, or I'm not following what you've said. I think this is more the type of solution I was looking for though. I've looking over the fields in myphpadmin, and I don't think I'm quite getting it. If you can explain it in more detail I'd appreciate it.