Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using an EAV like structure to store CSV files. There aren't any predetermined columns, so it needs to be able to expand the columns / data on demand. Is this a fairly good way of handling such a task, or is there a better/more efficient way of doing this?

Below is some sql structure / an example query that the application builds on the fly to help search through the data / display it.

CREATE TABLE IF NOT EXISTS `table` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `some_link_id` int(10) unsigned NOT NULL,
    `date_created` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `some_link_id` (`some_link_id`),
    CONSTRAINT `FK_some_fk_name` FOREIGN KEY     (`some_link_id`) REFERENCES `some_other_table` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table` (`id`, `some_link_id`, `date_created`) VALUES
    (111, 12, '2013-06-22 02:31:00'),
(112, 16, '2013-06-22 03:34:31');


CREATE TABLE IF NOT EXISTS `table_eav` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `data_id` int(10) unsigned NOT NULL,
    `value` text NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name_data_id` (`name`,`data_id`),
    KEY `FK_table_eav_table` (`data_id`),
    CONSTRAINT `FK_table_table_eav` FOREIGN KEY         (`data_id`) REFERENCES `table` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `table_eav` (`id`, `name`, `custom_data_id`, `value`) VALUES
    (495, 'name', 112, 'brian'),
(496, 'email', 112, 'email@email.com');

This is the query that gets built out automagically by the application[I get the distinct selection on the name column], I started with a stored procedure but needed to dynamically generate the columns on the fly....

select * from (select pcd.id, max(if(pcde.name = 'name', value, '')) as `name`, max(if(pcde.name = 'email', value, '')) as `email` from `table` pcd inner join `table_eav` pcde on pcde.data_id = pcd.id where pcd.some_link_id=16 group by pcd.id) as t1
share|improve this question
    
What processing/queries do you intend to perform on this content? How many columns/rows to you imagine as a maximum? –  Greg Robson Jun 22 '13 at 14:31
    
I don't do any server side processing, it comes formatted the way I want it from mysql. As for querying, I mainly take the bottom query and apply a like clause to one of the column names along with ordering/limiting. I would imagine 15 cols max by 200 rows per csv file, which equates to 3000 rows in the eav table –  Brian Putt Jun 22 '13 at 19:16

1 Answer 1

Okay, the amount of data is okay (assuming you don't want to query over 100s of tables!). I think the table structure needs changing a bit to make this easier.

tblCsvTable(csvTableID, name, dateCreated)
e.g. (1, "my CSV table", '2013-06-24')

tblCsvColumn(csvColumnID, columnName, keyCsvTable[FK])
e.g. (5, "name", 1)
(6, "email", 1)

tblCsvValue(csvTableID, keyCsvColumn, csvValue). (PK on [csvTableID, keyCsvColumn)
e.g. (1, 5, 'Geoff')
(1, 6, 'mail@geoff.com')
(1, 5, 'Steve')
(1, 6, 'Steve@mail.net')

At least then it's easy to get column names and do a LIKE on a particular column ID that you need.

Does that help?

share|improve this answer
    
This helps, it's definitely a better approach as it minimizes the number of rows that are created. I hope I understand the query over 100s of tables correctly, you're just referring to 100 or more csv imports as it would generate a large number of rows. I am definitely not creating a new table in the database per csv import ;D Also tried upvoting, I have less than 15 reputation :( –  Brian Putt Jun 24 '13 at 18:57
    
Thanks anyway for trying, I only started recently myself. Doesn't take long to unlock a lot of the features! –  Greg Robson Jun 24 '13 at 22:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.