Unit Inspection storage in SQL Server DB?

Question

I am no DBA. I do have quite a bit of experience in retrieving data out of a DB for reporting purposes, but the actual designing of one I have little to no experience with. So I am hoping someone here can give me some guidance on the best way to achieve what I am trying to do:

One part of the database that I need to design is the storing of inspections of housing units and buildings. A Building has one or more Units. A Building also has Features (out side of the units like Stairways, walkway, a roof, etc). A Building with one Unit is a house. An apartment building is a Building with multiple Units. A Unit is made up of Spaces (rooms, halls, living room(s), kitchen(s), etc) and Spaces are made up of Features (Doors, windows, floors, ceilings, walls, appliances, outlets, etc. Basically everything that would need to be inspected in a Unit). Setting up the relationships between these objects seems fairly straight forward to me, but I need to also include some sort of Inspection object(s). Both Buildings and Units will need to be inspected. Buildings and Units (which there can be 10,000+ units in a given DB and 100s of Buildings) needs to be able to have multiple inspections stored (Buildings and Units will need to be inspected at least once a year) for historical purposes. An Inspection will basically be made up of each Feature in a Building or a Unit (depending on if a Unit is being inspected or a Building). Keep in mind that the Features in a unit and a Building can be unique to that Unit or Building. What is the best way to implement the Inspection object(s) in the Building/Unit/Space/Feature relationship in a SQL Server DB?

I hope all of the above is clear! I can give more detail if it is not.

Thank you in advance on any help you can provide!

Ben

Answer 1

Personally I would have a structure like:

property
id INT INCREMENT(1,1)
name VARCHAR(255)
...

inspectionObject
id INT INCREMENT(1,1)
name VARCHAR(255)
...

propertyInspection
id INT INCREMENT(1,1)
date DATETIME
inspector INT (FK: inspector table)
propertyID INT (FK: property table)
...

propertyInspectionResult
id INT INCREMENT(1,1)
inspectionID INT (FK: propertyInspection table)
inspectionObjectID INT (FK: inspectionObject table)
resultID INT (FK: result table)

Something like that should be a good structure to go to, it then allows you to record the details for each inspection once, but have the many objects for an inspection also recorded. You can then undertake some pretty decent reporting based on the results and so on.

If you need any further information let me know.

Matt