Hello all,

A while ago we developed a database to inventory the trees planted in our district, and now the original client is interested in revisiting the project and expanding it's functionality. The original database was designed by someone else, but it's fallen to inexperienced me to implement the improvements. I'd appreciate any suggestions or critiques on what I've done here.

We're essentially looking at two things: the tree, and what the tree is growing in (such as a planter or an opening cut into the sidewalk, called a pit). I'm feeling ok with what I've done with the tree; what I'm concerned with is how to handle the second item, called the 'enclosure'.

I've attached E-R diagrams of the old schema and the changes I've made. Here's a synopsis of what I did:

The enclosure_class is something like a "pit" or "planter" and enclosure_type something like "mulch" and "concrete", so that when combined you'd have a "mulch pit" or "concrete planter". I left these alone.

Now, I know that the enclosure table steadily grew as new things were encountered during the initial inventory (for example, fencing and edging weren't originally included). Also, looking at the grate_manufacturer_id, fence_id, and edging_id columns, it seemed to me these were basically recording the same kind of information. So I decided that these were all essentially different types of landscaping and pulled them into their own table. The landscape_class table identifies the class of landscaping (fence, edging, grate) and the landscape table contains the descriptions previously listed in the grate, fence, and edging tables.

Finally, I pulled all the grate and fence repairs out into a separate table and changed the enclosure_condition column ('broken', 'good', etc.) to a foreign key to a type table.

So, I don't know. Did I do some good? Are there further improvements that can be made? I know it's probably a simple design, but this is my first real database design, so I'd appreciate feedback of any kind. Thanks!

I took some time to rewrite my original post in the hopes of eliciting some response. This is the first database I've had to work on, so I'd really appreciate any constructive comments or criticism anybody may have. Thanks!

well, IMHO, i think you probably better to give some printscreens from a CASE software so the database structures would be easier to analyze
would you like to?

Ok, I updated the first post again and included E-R diagrams. Hopefully this makes it easier to understand.

Somebody must have some comments! My design can't be that good!

I continue to push on in hopes of achieving enlightenment ....

Reconsidering my original design, I think I've missed the mark on a couple things:

- Although an enclosure can have multiple types on landscaping (including none), it can only have one of each class of landscaping. In other words, you can have a fence and a grate, but not two fences.

- It seems to me repairs are really related to the type landscaping -- it makes no sense to, say, "fix the fence" if there's no fence.

I've attached a diagram with an updated design. Table explosion!

I guess the original design wasn't as off as I thought, but I hesitate to pull everything back into the original enclosure table for a couple reasons:

- If we happen upon a new class of landscaping, the table needs to be modified to accommodate it. To me this raises a "not normalized" flag. I suppose you could argue it should also raise the "inadequate data analysis" flag -- my current design still requires some upheaval to add a new class of landscaping, but it seems to me that I've isolated where the change happens. I do struggle at times with the question of "is this a repeating pattern?" vs "have we really analyzed this thoroughly enough for all possible cases?"

- It doesn't enforce the relationship between landscaping and repairs. Although now I wonder how hard I've made it to answer the question, "what are all the repairs that need to be done?"

Suggestions, comments, criticisms much desired and appreciated!