Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I don't see an Asset table. –  ypercube Mar 30 at 0:09
    
And Employee Owns Asset sounds like a common 2-junction table, for a many-to-many relationship between Employees and Assets. But its design seems to be trying to model something else. –  ypercube Mar 30 at 1:07
    
@ypercube Asset Type table is Asset table. –  caramel1995 Mar 30 at 1:12
    
@ypercube but my Employee Owns Asset table links Asset Type Value Table, Asset Type Table and Asset Detail Table –  caramel1995 Mar 30 at 1:13
    
Exactly. It should link only Employees and Assets, nothing else. You probably should have some other link table between Assets and Asset Details and Asset Values, to designate what details and values an asset has. These properties of an asset are not related to whether an employeee owns it or not. –  ypercube Mar 30 at 1:23

1 Answer 1

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.

share|improve this answer
    
how if enforce a rule where for each employee, I will have to predefine the quantity of each asset that can be owned by them? For e.g employee A should only have 2 laptops. How should I record this data? –  caramel1995 Mar 30 at 15:25
    
@caramel1995 - This happens automatically. You don't assign a quantity of two laptops, you assign 2 specific laptops. This would manifest itself as two records in EMPLOYEE_OWNS_ASSET or as two ASSETs with an FK to EMPLOYEE depending on how you model it. –  Joel Brown Mar 30 at 16:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.