0

I have a database where I am trying to figure out how to join a table that contains an object called ModelType. The calibration table contains two fields (EffModel and BkgModel) that are based on a ModelType. I cannot figure out how to join these two fields. Below is an image of the stucture. I am trying to make sure that if I were ever to update one of the model types, that I would not have to update the reference Calibration.

I thought about creating a table called EffModelType and BkgModelType, which would separate the two fields depending on the same table but cannot for the life of me think of what would go in it.

database issue

Updated Info: I may have misspoken about EFFModel and BkgModel Being based on Model type. Essentially an example table model type is as follows:

ModelTypeID  | ModelName    |   ModelNumber
+++++++++++++++++++++++++++++++++++++++++++
     1       |  Linear      |       1
     2       |  Exponential |       2

and the EFFModel will be assigned a model type such as 1 (linear) and the BkgModel will also be assigned a model type, such as 2 (Exponential). Also the ModelNumber is an identifier from another application that will eventually be deleted so it can be ignored.

2
  • Can you please describe how EffModel and BkgModel are based on ModelType? Is there a calculation based on the Model?
    – Twinkles
    Apr 16, 2014 at 8:29
  • @Twinkles I have added information to the post.
    – ObieMD5
    Apr 16, 2014 at 15:32

1 Answer 1

0

I would examine your model and see if it needs some normalization.

It would appear that the two types of model are sub-types of a common model. I have found this kind of implementation requires some design. Using the same id sequencing for both tables opens up a number of options for implementation.

Creating an intermediate Model table containing the Model id, modelTypeID, and any common fields contained in the subtypes also helps. Depending on the query requirements you may be able omit either the subtype tables or the model table from some queries.

If there are few columns in the sub-type tables (EffModel and BkgModel) you may want to add them as nullable columns in the Model table. Alternatively, you can implement the normalized model and use a UNION query, or outer joins to both subtype tables.

In any case you will need to ensure the integrity of the data and ensure the records created match the ModelType.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.