My database contains around 20 tables that holds a user's information. For example it has
- Personal : hold user personal info
- Documents : uploaded files
- Activities :
- Etc..
Every table contains a user_Id
column for wiring them together ( one to many relationship), along with different table specific columns and constraints.
My question is how should I fetch all data for a single user from all these tables ?
Currently when ever I load user , application need to do
Select * from table1 where user_Id = x;
Select * from table2 where user_Id = x;
Select * from table3 where user_Id = x;
..etc
Since I'm using php (oop) its not a bad thing as every table has its own model that retrieve it. Yet I'm worried about performance as I currently run over 20 queries every time I load page. And since these data are very dynamically updated. Caching isn't helping much.
So what is the best methodology to fix this ?
example of table structures
CREATE TABLE IF NOT EXISTS `documents` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`collection` text NOT NULL,
`photo_date` timestamp NULL DEFAULT NULL,
`gallery` varchar(50) NOT NULL,
`cover` int(1) DEFAULT NULL,
`upload_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
CREATE TABLE IF NOT EXISTS `problemlist` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`visit_id` int(10) unsigned NOT NULL,
`pt_id` int(10) unsigned NOT NULL,
`problem` varchar(200) NOT NULL,
`severity` int(2) unsigned NOT NULL,
`note` text,
`solved` int(1) unsigned NOT NULL,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
CREATE TABLE IF NOT EXISTS `visits` (
`id` int(10) unsigned NOT NULL,
`pt_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`visit_date` timestamp NULL DEFAULT NULL,
`visit_end` timestamp NULL DEFAULT NULL,
`complain` varchar(250) DEFAULT NULL,
`dx` varchar(200) DEFAULT NULL,
`note` text,
`stats` enum('booked','waitting','finished','noshow','canceled','inroom') NOT NULL,
`deleted` int(1) DEFAULT NULL,
`booked_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`arrived_at` timestamp NULL DEFAULT NULL,
`started_at` timestamp NULL DEFAULT NULL,
`checkout_at` timestamp NULL DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=212 ;