DB structure foreign key

[cedtech23]

I am a newbie to database design and working of a database that consist of five tables

 

1. appointments

2. interpreters

3. patients

4. providers

5. requesters

 

the structure of the tables is shown below. should I add the primary keys from interpreters(intpre_id),

patients(pt_id), providers (prov_id), requesters(pt_id) to the appointment tables as foreign keys or regular int data type?  ???

 

 

mysql> show tables;

+-----------------+

| Tables_in_forms |

+-----------------+

| appointments    |

| interpreters    |

| patients        |

| providers      |

| requesters      |

+-----------------+

5 rows in set (0.00 sec)

 

 

mysql> describe appointments;

+-----------+---------+------+-----+---------+----------------+

| Field    | Type      | Null  | Key | Default | Extra          |

+-----------+---------+------+-----+---------+----------------+

| appt_id  | int(11)  | NO  | PRI | NULL    | auto_increment |

| appt_date | date  | YES  |      | NULL    |                |

| appt_time | time    | YES  |      | NULL    |                |

+-----------+---------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

 

mysql> describe interpreters;

+-----------+------------------+------+-----+---------+----------------+

| Field    | Type                  | Null | Key | Default | Extra          |

+-----------+------------------+------+-----+---------+----------------+

| f_name    | varchar(50)      | NO  |      |        |                |

| l_name    | varchar(50)      | NO  |      |        |                |

| intpre_id | int(10) unsigned  | NO  | PRI | NULL    | auto_increment |

+-----------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

 

mysql> describe patients;

+-----------+------------------+------+-----+---------+----------------+

| Field        | Type                | Null | Key | Default | Extra          |

+-----------+------------------+------+-----+---------+----------------+

| f_name    | varchar(50)      | NO  |    |        |                |

| l_name    | varchar(50)      | NO  |    |        |                |

| phone_num | varchar(11)  | YES  |    | NULL    |                |

| pt_id    | int(10) unsigned  | NO  | PRI | NULL    | auto_increment |

+-----------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

 

mysql> describe providers;

+---------+-------------+------+-----+---------+----------------+

| Field  | Type        | Null | Key | Default | Extra          |

+---------+-------------+------+-----+---------+----------------+

| prov_id | int(11)    | NO  | PRI | NULL    | auto_increment |

| f_name  | varchar(50) | NO  |    |        |                |

| l_name  | varchar(50) | NO  |    |        |                |

+---------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

 

mysql> describe requesters;

+--------+------------------+------+-----+---------+----------------+

| Field  | Type            | Null | Key | Default | Extra          |

+--------+------------------+------+-----+---------+----------------+

| f_name | varchar(50)      | NO  |    |        |                |

| l_name | varchar(50)      | NO  |    |        |                |

| req_id | int(10) unsigned | NO  | PRI | NULL    | auto_increment |

+--------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

 

 

 

 

 

[wsantos]

I don't see any relationship for that table on the rest of the tables.  In fact I don't see any relationship between all the tables.  Would you use this as a lookup table? It is always my habit to use id as indexed primary key.

[Barand]

I can see that an an appointment will involve a patient and may involve an interpreter (but the interpreter may be null if not required).

 

I don't know how providers and requesters are related to appointments as I have no idea what these definitions are.

 

As to INT or FK - if tables use myisam engine you have to use int. If innodb then use int but you can also define them as foreign keys. Either way you can still use them for relational joins.

[cedtech23]

The appoinments table will be used to capture information from the user. All fields are required and the provider, interpreters

must be in the providers, interpreters tables. I made the all fields NOT NUll.

 

Basically ever appointment will have a provider, patient, interpreter, requester, date and time.

 

 

 

 

 

mysql> describe appointments;

+-----------+---------+------+-----+---------+----------------+

| Field    | Type    | Null | Key | Default | Extra          |

+-----------+---------+------+-----+---------+----------------+

| appt_id  | int(11) | NO  | PRI | NULL    | auto_increment |

| appt_date | date    | NO  |    |        |                |

| appt_time | time    | NO  |    |        |                |

| pt_id    | int(11) | NO  |    |        |                |

| prov_id  | int(11) | NO  |    |        |                |

| intpre_id | int(11) | NO  |    |        |                |

| req_id    | int(11) | NO  |    |        |                |

+-----------+---------+------+-----+---------+----------------+

7 rows in set (0.00 sec)

[wsantos]

Int primary key would sound good to me.

[Barand]

There is an INT PRIMARY KEY on appt_id. You can only have one

[cedtech23]

how does one create a relationship between tables in mysql?

 

example

 

table appointments prov_id has a many to one with table providers id

[Barand]

Just join the tables on the keys

SELECT a.appt_date, a.appt_time, p.f_name, p.l_name, i.f_name, i.l_name
FROM appointments a
INNER JOIN patients p ON a.pt_id = p.pt_id
INNER JOIN interpreters i ON a.intpre_id = i.intpre_id