First of all i would like to say that i have been working for a long time with CODEBASE(XBASE), which is a NOSQL database. And i have recently moved to DB2
I used to have a single table with almost 300 columns representing the data related to cars production. A single record of that table used to contain every information related to a single car production.
The only problem was that i need to change the number of columns in relation with the model of car. For example for ferrari i need 300 columns and for lamborghini 260 columns to store the data. So every time when we have a new production plant of a new car i need to modify my table in relation with number of data to store.
When i started using Db2 i decided to split the old table into 2 different tables, because i realised that there were fix columns and other that vary in relation with the production plant.
So i created 2 tables: GENERAL_INFO and DATA.
GENERAL_INFO contains columns like PRODUCTION_DATE, MODEL, CAR_CODE(Primary Key), ecc.
DATA contains CAR_CODE, DATA_DESCRIPTION, DATA_VALUE.
So with this new solution the information related to a car is stored in a single row of table GENERAL_INFO and x rows of table DATA.
The problem is that i would like to work with a single table from the outside even if the data is stored in 2 different table. Because to get every information related to a car i need to do a single SELECT on the table GENERAL_INFO and a lot of SELECTS on the table DATA to get that 300 columns that i used to have with my old table.
I would like to know if i can create a VIEW on my 2 tables to see as an unique table from outside? Or otherwise is there another solution to solve it?