I have written Perl code that parses a text file and uses a hash to tally up the number of times a US State abbreviation appears in each file/record. I end up with something like this.
File: 521
OH => 4
PA => 1
IN => 2
TX => 3
IL => 7
I am struggling to find a way to store such hash results in an SQL database. I am using mariadb. Because the structure of the data itself varies, one file will have some states and the next may have others. For example, one file may contain only a few states, the next may contain a group of completely different states. I am even having trouble conceptualizing the table structure. What would be the best way to store data like this in a database?
There are many possible ways to store the data.
For sake of simplicity see if the following approach will be an acceptable solution for your case. The solution is base on use one table with two indexes based upon id and state columns.
CREATE TABLE IF NOT EXISTS `state_count` (
`id` INT NOT NULL,
`state` VARCHAR(2) NOT NULL,
`count` INT NOT NULL,
INDEX `id` (`id`),
INDEX `state` (`state`)
);
INSERT INTO `state_count`
(`id`,`state`,`count`)
VALUES
('251','OH',4),
('251','PA',1),
('251','IN',2),
('251','TX',3),
('251','IL',7);
Sample SQL SELECT output
MySQL [dbs0897329] > SELECT * FROM state_count;
+-----+-------+-------+
| id | state | count |
+-----+-------+-------+
| 251 | OH | 4 |
| 251 | PA | 1 |
| 251 | IN | 2 |
| 251 | TX | 3 |
| 251 | IL | 7 |
+-----+-------+-------+
5 rows in set (0.000 sec)
MySQL [dbs0897329]> SELECT * FROM state_count WHERE state='OH';
+-----+-------+-------+
| id | state | count |
+-----+-------+-------+
| 251 | OH | 4 |
+-----+-------+-------+
1 row in set (0.000 sec)
MySQL [dbs0897329]> SELECT * FROM state_count WHERE state IN ('OH','TX');
+-----+-------+-------+
| id | state | count |
+-----+-------+-------+
| 251 | OH | 4 |
| 251 | TX | 3 |
+-----+-------+-------+
2 rows in set (0.001 sec)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments