From One-to-Many to Many-to-Many

In this article we will talk about ONE-TO-MANY and MANY-TO-MANY relationships between tables.

In this article we will talk about ONE-TO-MANY and MANY-TO-MANY relationships between tables.

 

One-To-One.

There is also a one-to-one relationship, but this type of relationship mostly relates to data storage features than to data relationship building. Sometimes it is not possible to store large amounts of data in the database, therefore some data, particularly separate columns, are transferred into another table. For example, we can transfer into a separate table BLOB/TEXT columns with binary or large text data. Rows in these tables are linked by primary key values.

 

One-To-Many.

Let us assume that we need to enter into the database information about store goods, the data should be split into groups to the separate categories. Let us create a table Category and enter the following information into it:

The column categoryId is unique and is an identifier for finding entry on the category.

Now, for each category we need to enter information about the product. As an option – let us try to add additional columns to the table Category, as a result we will get something like this:

As we can see, there is information redundancy – category data are repeated for each product. Just imagine, how the database will enlarge with such type of storage. In addition, it is inconvenient to modify such table, because it is required to use a pair of unrelated columns to identify the unique table recod – it is categoryId and productId.

In general, such a table needs data normalization. To do it we well leave the Category table in its original form, and create a second table Product with the product data. To specify the product category it is needed to add a column with the category number from the table Category into the Product table, let us call this column categoryId, it will be used for linking/referencing the Product and Category tables.

Having such data organization you can easily make various kinds of selection from the database, for example, to display all products of the \’Keyboard\’ category, you can use the following query –

On the diagram the Product and Category tables will be as follows:

From One-to-Many to Many-to-Many

 

In this example, the Category.categoryId and Product.categoryId fields have one-to-many relationship, i.e. one category has many products. In practice, the such columns are linked by a foreign key for relation between the parent and child tables. A foreign key is used to maintain constraints of data referential integrity, e.g. for constraint of invalid data entering.

 

Many-To-Many.

Let us discuss many-to-many relationship. The implementation is more complicated, but is not too difficult. We will use the same examples. In the previous section, each category contained a number of products and each product belonged to one of the categories, and now the product can belong to several categories at once, we will complicate a task and add few new categories in the Category table, this way our task will be more practical.

Output of the table Category:

Output of the table Product:

In other words – a single product can belong to several categories, and each category can contain multiple products. In the database, entry of one table is linked to multiple enties of another table, and vice versa. To provide a many-to-many relationship it is needed to create the third table for storing the relationships between the categories and products, let us call the table – ProductCategory.

Output of the table ProductCategory:

Let us examine the case with devices – mouse:

  • Category \’Mouse\’ contains multiple mice – \’Mouse A\’, \’Mouse B\’, \’Mouse C\’.
  • Each mouse belongs to \’Mouse\’ category and a number of other categories. \’Mouse A\’ with productId = 1, is included into the \’Mouse\’, \’Wired\’ and\’ PS /2\’ categories.

Summary table ProductCategory contains category and products numbers, which are the primary keys of the first two tables. These relations hips are supported by the two foreign keys to the corresponding CategoryandProduct table columns. Note that a pair of column values productID-categoryID is unique, and therefore for these columns you can create a primary or unique key.

From One-to-Many to Many-to-Many

 

Example.

The script of tables creation:

The example of data selection: the query outputs product list with prices for \’Mouse\’ category –

Leave a Reply

Your email address will not be published. Required fields are marked *