Is there a general guideline that tells when to create a separate table?
For example, in my database I have a table Users with a column IDCity, that is a foreign key to the Cities table, which has ONLY two columns: IDCity (the primary key) and CityName (the complete name of the city).
In this case, when I create the schema in QlikView, is it better to create two separate tables, linked via the IDCity field, or to include the CityName field directly into the Users Table? Please note that the cities I manage in my database are a small set (about 100).
Keeping data in seperate table or not depending ur requirments.
I m explaining some scenerio, u need to decide.
1. If you have Table 'A' having huge data(Transaction) and table 'B' consisting the small data (Master) with few fields then u can use Left Join.
The Left join having following advantages and disadvantages.
A. The left join improves the performance of chart refreshed(Front end speedup), but slowdown the loading process.
B. Data reduced as per Transaction table i.e. if the Link Key is not exists in Table 'A' then report will not reflect the corresponding data from table B.
C. If Master data table 'B' having non unique Key(Linking field) field then Transaction data may multiply. i.e. if A table Key=1001 and Amoutn =1000, and 'B' table has Key=1001 repeats for twice then Amount of table A reflects for twice. Must take care while Joining.
2. Linking the table 'A' and 'B'
We can link the table while Master having the large data.
we can also use Left Keep--- while linking (Equivalent to the Left Join, will reduce the data as per Transaction table but keep table seperate)
The linking table will not harm to data of transation or master table but ur chart consisting the fields from both table then performance goes down.