Create Connected Tables

Now we will deal with connected data put in different tables.

So, the first step is to create more than one table and connect them, so that each row in one table can reference another row in the other table.

We have both of the tables new_roots and users.

users

id	email	first	last	username
1	colrc@gmail.com	Lawrence	Nicodemus	original
2	jdoe@gmail.com	John	Doe	jdoe
3	jadoe@gmail.com	Jane	Doe	jadoe
null	rdoe@gmail.com	Robert	Doe	rdoe

new_roots

id	userId	english	nicodemus	root
1	1	hello.	a	a
null	1	so. (lit. Is that so?),	a?	a
null	1	cut out, knock off!, quit, stop. (lit. Cut it out!, Knock it off, quit it, Stop it!),	aaaa...!	a
2	2	hey.	aye	a
3	2	boots.	buts	bc
4	3	boots (to be wearing...). (lit. He is wearing boots)	etsbutsbutsshn	bc
5	3	boot. (lit. a borrowed root)	sbutsbutsshn	bc
6	1	rubber boots (putting on...).	sbutsbutsshnmsh	bc
7	1	bull.	buuli	bl
...	...	...	...	...

We created a userId column to hold the id from the user table and reference all of the user data by this id. The new_rows table knows about the users table, but not vice versa. Although we can still turn this relationship around if we want to. More about this later.

By the way, the idea of table referencing each other is the reason why we call these types of databases referential databases.

The relationship of the tables can be described like this:

• users->roots can be described as one-to-many. Many roots could have been entered or edited by one user. So, each row in the users table corresponds to potentially many roots.
• At this point, we haven't specified that a root HAS to reference a user, only that it MIGHT. In database terms, this means that the reference to a user from the roots table might be NULL.

This way each row in the table roots can point to a row in the table users:

One-to-Many and Many-to-One

Here we are creating connected data in a relationship where one user could have many roots. So it is commonly called a one-to-many or many-to-one relationship.

The many-to-one part can be seen if we start from the roots, many roots could be maintained by one user.

This is probably the most popular type of relationship, so we'll start with that. But there's also many-to-many and one-to-one relationships.

Recap

We already have these tables, and they are implicitly connected by the userId column.

Now that we have the tables created and connected, let's read the data. 🚀