3 way junction table and redundant value

database-design

I was constructing a conceptual design for database in relational model until I stumbled upon some issue. My progress so far is:

Asset Type Table

+---------------+-----------------+
| asset_type_id | asset_type_name |
+---------------+-----------------+
|             1 | Computer        |
|             2 | Keycard         |
+---------------+-----------------+

Asset Detail Table

+-----------------+-------------------+
| asset_detail_id | asset_detail_name |
+-----------------+-------------------+
|               1 | Model             |
|               2 | Brand             |
|               3 | ID                |
+-----------------+-------------------+

Asset Type Value Table

+----------------+-------------+
| asset_value_id | asset_value |
+----------------+-------------+
|              1 | XPS 15      |
|              2 | Dell        |
|              3 | L760I       |
|              4 | MSI         |
|              5 | 302B        |
|              6 | 301A        |
+----------------+-------------+

Employee Owns Asset Table

+-------------+----------+---------------+-----------------+----------------+
| employee_id | quantity | asset_type_id | asset_detail_id | asset_value_id |
+-------------+----------+---------------+-----------------+----------------+
|        3100 |        2 |             1 |               1 |              1 |
|        3100 |        2 |             1 |               2 |              2 |
|        3100 |        2 |             1 |               1 |              3 |
|        3100 |        2 |             1 |               2 |              4 |
|        3100 |        1 |             2 |               3 |              5 |
|        3200 |        1 |             2 |               3 |              6 |
+-------------+----------+---------------+-----------------+----------------+

The Question

1.The business rule is that there are many asset type (computer, keycard, laptop and etc) given by the company to the employee and the company wish to keep track of what is given. So it's up to the company website admin to add in any kind of asset type into the database. Each asset has arbitrary number of details(brand, model and etc) that can be specified by the admin anytime(add more detail for an asset or delete a detail anytime). An employee can have 0 or more quantity of the same asset type (e.g an employee is given 2 laptops by the company) and the corresponding detail's value should be entered too.

2. As you can see, here I have constructed a 3 way junction table Employee Owns Asset Table. I just wonder is it appropriate to have this kind of table since all the while I have only seen a 2 way junction table. Besides that , in my junction table my Quantity field keep having redundant value. I try to solve the problem but to no avail.

Thanks for spending time reading my question.

Best Answer

You need to normalize your schema. Your Employee_Owns_Asset table is trying to do too many things. Consider this schema:

ERD

Note that your individual assets need to be normalized out into their own table. This lets you separate the two semantically independent pieces of information:

  • What are the properties (brand, model, etc.) of each asset?
  • Who (if anyone!) owns this asset at a particular point in time?

This is a case of a requirement for fourth normal form (4NF), where it is important to isolate independent many-to-many relationships.

Note that if you don't want to track an asset's owner over time, then you could simplify the illustrated model and make ASSET.owning_employee_key a foreign key on the ASSET table and eliminate the EMPLOYEE_OWNS_ASSET table from the model.

Related Question