recycling data database design

Question

I am struggling to come up with a proper design to hold recycling data. The data is collected on a three month cycle, for each week of that month. so the data looks like the following (they collect the data currently in excel right now):

    Month 1     | Plastic       | Metal  | Newspaper |  Cardboard | Paper
 ---------------------------------------------------------------------------------
   Measurement: | (x) 19 Gal    | 19 Gal | 19 Gal    |  19 Gal    | 19 Gal
                |   36 Gal      | 36 Gal | 36 Gal    |  36 Gal    | 36 Gal 
                |   Lbs         | Lbs    | Lbs       |  Lbs       | Lbs   
                |   Other:(txt) | Other  | Other     |  Other     | Other                      
 ---------------------------------------------------------------------------------
   Week 1       |    1
   Week 2       |
   Week 3       |
   Week 4       | 
   Week 5       |    2
 ----------------------------------------------------------------------------------
    Total (Lbs) |    Z

In this sample, the total (Z) would be a conversion of 3 19 gallons bins to lbs

The part that is making my head hurt is each Recyclable product also has multiple attributes attached to them, so Plastic has a bin size, how it was recycled, etc....

I have read up on EAVs and class Table inheritance, but they don't feel 'right' for this problem. Thanks in advance.

Answer 1

Your case looks like an instance of the Gen-Spec design pattern. Gen-spec is familiar to object oriented programmers through the superclass-subclass hierarchy. Unfortunately, introductions to relational database design tend to skip over how to design tables for the Gen-Spec situation. Fortunately, it’s well understood. A web search on “Relational database generalization specialization” will yield several articles on the subject. Some of your hits will be previous questions here on SO.

The trick is in the way the PK for the subclass (specialized) tables gets assigned. It’s not generated by some sort of autonumber feature. Instead, it’s a copy of the PK in the superclass (generalized) table, and is therefore an FK reference to it.

Thus, if the case were vehicles, trucks and sedans, every truck or sedan would have an entry in the vehicles table, trucks would also have an entry in the trucks table, with a PK that’s a copy of the corresponding PK in the vehicles table. Similarly for sedans and the sedan table. It’s easy to figure out whether a vehicle is a truck or a sedan by just doing joins, and you usually want to join the data in that kind of query anyway.

Answer 2

Don't you want something like:

Month|Recycling_Type|Recycling_ID

---

Week1,Plastic,2342
Week2,Plastic,2343
Week2,Metal,4

Then have a seperate Table for each Recycling type:

Plastic
ID,Bin Size

---

2342,5
2343,6

etc

Answer 3

For a start, please tell me that you are not using "Week 1" etc., ever, but an Interval (i.e. int-start date, int-end date).

Now... what are your problems with Table Inheritance? This look a classic example. The base class/table would be Recyclable product with a modicum of common attributes... is this the problem? Can't find anything "in common", apart from the name?

What about the unit of measurement? I.e. a reference to another table detailing Kg., Gallons, cubic foot, etc.?

Maybe an "Hazardous" flag?

From then you can specialize "Plastic" adding a field for "Recycle method". Which points to another table listing the available alternatives. "Bin Size" would be a unit of measurement? Should this be a basic "class" attribute (i.e. all plastics are measured by weight) or should it depend on different things? (i.e plastic type? collecting point type?)

It's perfectly ok non to have much in the base table apart from a numeric ID and some descriptive fields, anyway.