1

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. enter image description here

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?

enter image description here

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

MVC Newbie
  • 365
  • 4
  • 9
  • 2
    Are you trying to model the entities in the subject matter at the conceptual level, or are you trying to model the tables in your DB design at the logical level? ER diagrams have been used for either of these, but if you don't know which you are doing, you'll end up confused. – Walter Mitty Jun 12 '14 at 12:29
  • To answer the question in the title, you don't model look up tables at the conceptual level or the logical level. At the physical database level, you diagram look up tables separately. This keeps the diagrams less confusing, especially for systems with dozens of look up tables. – Gilbert Le Blanc Jun 12 '14 at 12:50
  • @Gilbert Le Blanc, the way I learned it, some 30 years ago, you do model lookup tables at the logical level. That may have changed over time. I note that Data Architect, a modeling tool from about 15 years ago, had only a conceptual model and a physical model. – Walter Mitty Jun 12 '14 at 17:59

1 Answers1

0

You are modelling a DBMS's metadata design, but not your application.

See these two posts' questions and answers.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83