Looking at your comments, I can see that the problem is not the JOIN
s, but the LIKE '%term%'
operators.
There are several options here, but assuming you are using MyISAM for your tables, or InnoDB and a MySQL version equal or newer than 5.6, you may use FULLTEXT indexes. MySQL implementation is not perfect, but it will work way better than using '%LIKE%'.
mysql> CREATE TABLE person (id SERIAL, first_name VARCHAR(100));
Query OK, 0 rows affected (0.13 sec)
mysql> insert into person (first_name) VALUES('AARON'), ('ABDUL'), ('ABE'), ('ABEL'), ('ABRAHAM'), ('ABRAM'), ('ADALBERTO'), ('ADAM'), ('ADAN'), ('ADOLFO'), ('ADOLPH'), ('ADRIAN'), ('AGUSTIN'), ('AHMAD'), ('AHMED'), ('AL'), ('ALAN'), ('ALBERT'), ('ALBERTO');
Query OK, 19 rows affected (0.01 sec)
Records: 19 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE person ADD FULLTEXT INDEX(first_name);
Query OK, 0 rows affected, 1 warning (0.73 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> SELECT * FROM person WHERE MATCH(first_name) AGAINST ('+al*' IN BOOLEAN MODE);
+----+------------+
| id | first_name |
+----+------------+
| 17 | ALAN |
| 18 | ALBERT |
| 19 | ALBERTO |
+----+------------+
3 rows in set (0.00 sec)
Please note that it is not exactly the same query- it will only match whole words, it may have length restrictions, controlled by ft_max_word_len
and ft_min_word_len
(or innodb_ft_min_token_size
and innodb_ft_max_token_size
) and stop words are into play.
According to database normalization technique create a separate for city, state and country. However if you're having only few records in company address table and there is no other tables are need address info, I think there is no point creating multiple tables.
Best Answer
The second approach is much better. One massive advantage is that both patient and emergency contact could have the same address. Typically an emergency contact is a parent or spouse, so you could have a checkbox in your application that says "Emergency contact's address is same as Patient's". This would save on space in the database, add convenience for users of your application without any extra development effort from yourself.