0

My database contains around 20 tables that holds a user's information. For example it has

  1. Personal : hold user personal info
  2. Documents : uploaded files
  3. Activities :
  4. 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 ;
Zalaboza
  • 8,899
  • 16
  • 77
  • 142
  • @JoeWatkins joining 20 tables with average 4-9 column each ? – Zalaboza Dec 08 '14 at 14:16
  • 20 is a bit much, but I'm assuming you hadn't heard of joining, and once you realize joining is a thing, you would optimize your structure to reduce the number of tables and complexity of the query. – Joe Watkins Dec 08 '14 at 14:42
  • Why loading all data on one page? Why not load on demand? – Mohsen Heydari Dec 09 '14 at 08:03
  • Unfortunately its a medical app. Page I'm taking about is the patient history, all data need to be visible so Dr. Can make his judgment. Edit, add more activity ( treatment, signs etc). – Zalaboza Dec 09 '14 at 08:43
  • You need to read an introduction to database design. – philipxy Dec 14 '14 at 08:32

0 Answers0