I'm trying to create a DB that can 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 occured.
Here's my confused ER- diagram.
Where I'm getting confused, is, how would/should I go about recording/referencing the pre-defined characteristics [found in the characteristic's table] in the Item_Observation table, as I would have to create another many-to-many table to hold such, but feel i'm not implementing it very well due to table duplication somewhat? But then I feel - I'm not 100% sure why - storing the observed characteristic's data in the look-up table iteself, is also not a good idea?
Which begs the question, should Look-up tables be left alone as a separate entity? And probably more to the point, is it my schema that's completely flawed? If you haven't already guessed, I'm certainly no DB designer. Thanks in anticipation, cheers Dyr
You are modelling a DBMS's metadata design, but not your application.
See these two posts' questions and answers.