Mysql Experts Please suggest/comment on my DB design
Posted by: Wane cooper
Date: November 20, 2009 12:38AM

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

Options: ReplyQuote


Subject
Written By
Posted
Mysql Experts Please suggest/comment on my DB design
November 20, 2009 12:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.