Mysql Experts Please suggest/comment on my DB design
Hello,
Ours is a java application which works with multiple vendors and their different services and needs weekly latest db updates from them. Means we upload the databases given by them on a weekly basis in to our db. At times these vendors change there table structures and in turn we too modify our tables to accommodate the change and our code too. There are times when vendors go away and new vendors come in. As you can see that ours is a very volatile business. We used the exact table structure given by the vendors which was creating a mess/re-work every time. In order to avoid this we came up with dynamic structure.
We created a
1)vendor table
2)Vendor services table
3)Vendor and service mapping table.
4)Store just all the vendors table names in one table.
5)Store all vendors table columns in one table.
6)Store all vendors column data in one table.
So the columns became each row in this new design. Here are the tables.
CREATE TABLE `third_party_vendors` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `third_party_vendor_service_type` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
CREATE TABLE `vendor_service_mapping` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`third_party_vendors_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`third_party_vendor_service_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_third_party_vendors_id` (`third_party_vendors_id`),
KEY `fk_third_party_vendor_service_type_id` (`third_party_vendor_service_type_id`),
CONSTRAINT `vendor_service_mapping_ibfk_1` FOREIGN KEY (`third_party_vendors_id`) REFERENCES `third_party_vendors` (`id`),
CONSTRAINT `vendor_service_mapping_ibfk_2` FOREIGN KEY (`third_party_vendor_service_type_id`) REFERENCES `third_party_vendor_service_type` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `virtual_table_name` (
`id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`table_name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `virtual_column_name` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`virtual_table_name_id` tinyint(2) unsigned NOT NULL,
`vendor_service_mapping_id` smallint(5) unsigned NOT NULL,
`column_name` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_virtual_table_name_id` (`virtual_table_name_id`),
KEY `fk_vendor_service_mapping_id` (`vendor_service_mapping_id`),
CONSTRAINT `virtual_column_name_ibfk_1` FOREIGN KEY (`virtual_table_name_id`) REFERENCES `virtual_table_name` (`id`),
CONSTRAINT `virtual_column_name_ibfk_2` FOREIGN KEY (`vendor_service_mapping_id`) REFERENCES `vendor_service_mapping` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `virtual_column_data` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`virtual_column_name_id` smallint(5) unsigned NOT NULL,
`data` tinytext,
PRIMARY KEY (`id`),
KEY `fk_virtual_column_name_id` (`virtual_column_name_id`),
CONSTRAINT `virtual_column_data_ibfk_1` FOREIGN KEY (`virtual_column_name_id`) REFERENCES `virtual_column_name` (`id`)
) ENGINE=InnoDB;
I hope you must have got a picture what i am trying to do. The virtual_column_data will hold a every vendors column data and each column data becomes a single row.
Advantages
-----------
1) I dont have to change the my code or db structure
2) Irrespective of any vendor table structure changes or any vendor leaving or coming in. there will not be any profound change.
3) As this table grows over a period of time i am planning on apply partitioning on the last table.(i know partitioning does not support foreign key, but got a work around that too).
I want mysql experts to please guide me or suggest is my approach correct or not.What are the various draw backs to this approach. Or Is this approach at least practical in nature in long term perspective...
Thanks