Let's keep going with our employees example and define a table that relates to names: addresses
create table addresses (
id int unsigned not null,
nameId int unsigned not null,
street varchar(252) null,
city varchar(64) null,
state varchar(2) null,
zip varchar(12) null
) engine=innodb default charset=latin1;
alter table addresses add primary key (id);
alter table addresses modify id int unsigned not null auto_increment;
What is a database relationship? Why do we need to relate data in one table to another? How does this table relate to the employee names table? All good questions. Let's answer them.
A database relationship is basically taking two tables and finding a common thing between them. For example, you could relate two tables together by using a lastname. You can also relate two tables using a number. In our example here, we're using the employee name table id field to relate to addresses. We basically associate the two tables together based on an employee id. The employee name id is a unique number that isn't duplicated for any other employee.
In some instances having related data can be great and other times it can introduce unwanted complexity. Using a database this way allows you to break up your data. It also allows you to separate your concerns. It's also a great way to store multiple entries for one employee. For example, in our addresses example, we can associate multiple addresses back to one employee. That can be extremely helpful. There are a lot of other reasons to use relationships in your data that will become more self apparent as time goes on and you become more advanced in your studies.
In our employee names and addresses tables, we can relate back using our names table id and our addresses table nameId. The numbers in those two columns will match if the address has been assigned to an employee.
We'll work directly with these relationships in the near future.