Hey Guys, i'm trying to conceptually model a db for the following situation:
To manage/record sightings of many differing machinery types, be it Cars, Buses, Trucks, Boats, Trains, etc. which will also record all the characteristics of such a sighting [which would obviously vary greatly: colour, hull type, vehicle model, etc., etc.] and where the sighting occurred, and by which User.
The data they enter/select will be mostly sourced from lookup tables, with perhaps only a Notes field for very specific observations and Photos taken of the observation being the exceptions. Note: I don't want users to record sightings of machinery that aren't in the DB, nor add new ones - though they should be able to access the data to know what the machinery, is, in the first place.
Please see my rough concept excert as per attachment-
What i'm struggling with, is - I feel like I'll be creating almost two separate DB's with mostly the same tables, therefore - surely my understanding of the situation is flawed, or currently modelled imperfectly due to redundancy [Green Tables]. The most obvious solution, I suppose, would be to use the tables currently implemented as the turquoise look-up tables to hold the observed data and add a column to the tables differentiating them as Observed records -- but that will spell trouble in a situation where the club often "up" votes better photos of Machinery to become the Look-up tables defaults, therefore, the original photographer would not then be allowed to delete his own photo that has become a default; I feel this could lead to many other 'erroneous' issues and become messy fairly quickly.
The other concept i'm struggling with is how to keep the initial observation unique, but allow additional updates over time. i.e. I don't want multiple observations of the same machine recorded at the same time, yet, I do want updates [has it sustained damage / changed colour / record the different locations it has been seen in etc.] -- for instance, most machinery can "move" but some are most definitely "stationary" [e.g. Tower Cranes], so recording observations from different users of the same Tower crane is superfluous [the same *anything for that matter], but if the crane's tower has been increased/decreased a story or more, we would want the ability to record that. Perhaps some Date Table, or adding a date attribute to the ObsCharacteristic Table?
So I suppose in summation: Should you ever combine "Look-up" table data with externally collected data in the same table - seems like a bad idea?
And what schema could you suggest to handle such a situation as appending further sightings to an already sighted Machine - another one-to-many table?
Sorry for the long-winded post, but I'm getting frustrated with learning concepts and understanding them in theory, but when it comes to implementation - I find myself getting awfully muddled. Thanks for any thoughts, suggestions in anticipation - cheers Dyr.