[SOLVED] Closure table with multiple data types?

Closure table with multiple data types?

I've been brushing up on my MySQL lately and I need to make a database with hierarchical data.

I have several different types of data that need to be represented in a tree format, but don't know how to go about doing it.

For example, Let's say I have a person, who can employ, or be employed by other people. Each of these people may have equipment checked out to them, and each piece of equipment must have a name, description, and a list of replacement parts, and each replacement part must have a cost, etc. etc.

Most examples of closure tables I see focus on how awesome they are for handling forums, or threaded-comments. How do I go about making a closure table that has multiple data types?

Solution

Here's a quick and dirty example:

select * from person

| pID | name      | employedBy |
+-----+-----------+------------+
|   1 | John Doe  |          2 |
|   2 | Joe Smith |       NULL |
|   3 | Meg Ryan  |          3 |

select * from equipment

| eqID | eqName   | eqDescription     | eqOwner | eqCheckedOutTo |
+------+----------+-------------------+---------+----------------+
|    1 | stuff    | just some stuff   |       3 |           NULL |
|    2 | table    | a table           |       1 |           NULL |
|    3 | computer | PC computer       |       3 |              2 |
|    4 | 3table   | table with 3 legs |       2 |           NULL |

select * from parts;

| partID | partName     | partCost |
+--------+--------------+----------+
|      1 | desktop1     |   499.99 |
|      2 | monitor13x13 |   109.95 |
|      3 | windows95    |    10.00 |
|      4 | speakers     |    30.00 |
|      5 | tabletop     |   189.99 |
|      6 | table leg    |    59.99 |

select * from equipmentParts

| epID | eqID | partID | quantity |
+------+------+--------+----------+
|    1 |    3 |      1 |        1 |
|    2 |    3 |      2 |        2 |
|    3 |    3 |      3 |        1 |
|    4 |    2 |      5 |        1 |
|    5 |    2 |      6 |        4 |
|    6 |    4 |      5 |        1 |
|    7 |    4 |      6 |        3 |

They you can query like:

select name,eqName,e.eqID,partName,partCost,quantity,(quantity*partCost) AS totCost
from person p
inner join equipment e ON e.eqOwner=p.pID
inner join equipmentParts ep ON ep.eqID=e.eqID
inner join parts pa ON ep.partID=pa.partID

| name      | eqName   | eqID | partName     | partCost | quantity | totCost |
+-----------+----------+------+--------------+----------+----------+---------+
| John Doe  | table    |    2 | tabletop     |   189.99 |        1 |  189.99 |
| John Doe  | table    |    2 | table leg    |    59.99 |        4 |  239.96 |
| Meg Ryan  | computer |    3 | desktop1     |   499.99 |        1 |  499.99 |
| Meg Ryan  | computer |    3 | monitor13x13 |   109.95 |        2 |  219.90 |
| Meg Ryan  | computer |    3 | windows95    |    10.00 |        1 |   10.00 |
| Joe Smith | 3table   |    4 | tabletop     |   189.99 |        1 |  189.99 |
| Joe Smith | 3table   |    4 | table leg    |    59.99 |        3 |  179.97 |

or summarize the cost of each equipment:

select name,eqName,sum(quantity*partCost) AS totCost
from person p
inner join equipment e ON e.eqOwner=p.pID
inner join equipmentParts ep ON ep.eqID=e.eqID
inner join parts pa ON ep.partID=pa.partID
group by e.eqID

| name      | eqName   | totCost |
+-----------+----------+---------+
| John Doe  | table    |  429.95 |
| Meg Ryan  | computer |  729.89 |
| Joe Smith | 3table   |  369.96 |