Problems with complex table structure in SQL few tables with similar fields.

[#1]

Hi,

I have few tables in my DB, that have same fields name + some extra + the _id fields.

I have used Qualify to make it so qv doesn't lik all the fields, but I need some of the ID filds to be linked just to 1 single table, and not to all.

Let say I have this:

Table1 (tbl_id, field1, field2, field3, type_id, currency_id)

Table2 (tbl_id, field1, field2, field3, type_id, currency_id)

Type (type_id,field1, field2)

Currency (currency_id, field1, field2)

so basicly I need Table1 and 2 to be liked to each other just by the tbl_id, while Table1 and 2 must be liked to Type and Currency, but no to each other (type_id and currency_id).

Hope someone could help.

The only solution so far is to add separated field_ids for each table that have it, but that will make like editint 50+ tables and adding 20-40 new fields to the index table and making it pointless having the separate tables for all different Type and Currency Characteristics.

Hope ther is a better way to make this.

[kouroshkarimi]

Does Table 1 and Table 2 have the same structure? If so it would be best to concatenate them.

[#3]

You'll have to duplicate the Type and Currency tables.  so have a tbl1_Type tbl2_type etc.It's not ideal as you're doubling the data you are storing for those types but it's the only way to link to the 'same' thing without linking those hwihc connect together.

Or split yout Table1 and Table2 tables somehow to that you can link out in the snowflake format without linking back together.... It's a dificult one without knowing more about the data itself as it can be difficult to work things out when everything is so generic.

I'll keep thinking about it and get back to you if I find a better solution...

[#4]

thx for the answer aeori007, that is what I came up with, but I have like way too many ID fields in way too many tables. The idea is that in the different tables I have different codes for the field type_id. So 1 record in tbl1.type_id is different then the tbl2.type_id and they multiply the records once qv links them, as they have tbl1.tbl_id=tbl2.tbl_id, but different type_id.

Hope this clears things up.

I have like 20 tables that have fixed data stored for different items, let say for a store that will be the products with ID, and lots of characteristics of each product that are fixed (until new product arrives), a second will be customers (ID, name, address and so on)

and then u have orders that have the ID of the user, and then u have different table for like let say somethign else (idk exactly what, but let say for the purpouse of the esample) table for Payments. there we have again the user_id, but it is not going to be the same as in the order.

So we have somethign like this:

Table1 many-to-one users one-to-many Table2
Table3 many-to-one ---//--- one to many Table4

... and so on till liek 20 tables all linked to table Users, but not to each other.

then another one that all the 20 tables + some more are linked to currency.

and so on like 30 times.

Hope this clears it out. is I have table users to have 100 extra fields, that will make it pointless of having that table, as it will slow and now optimize the DB.

[#5]

Looking around I just noticed that there is a SourceTableView in the TableView, where I can see all the wrong connections, and if there is a way to make and cut/delete them, that will be perfect.

[#6]

BUMP~

So you want to tell me that there is no solution to this problem?

[#7]

Hi apologies for the time taken in getting back to you.

The source and table views don't allow you to cut/delete connections unfortunately.

It sounds like you ensuring the fields within your tables will be important so, taking the shop example, if you have Customer & Product, both with an ID field ensure they are named cuts_ID and prod_ID.

Qlikview links on variables with the same name so making sure that things you don't want link are named different should ensure this.

Have you considered combining your user and currency tables into one 'Master' table? So the information required to link to is in the one table and everything links to that?

This is quite a difficult problem to solve without having access to your data source. It sounds like it could be solved by ensuring all of the fields are named appropriately so that they can't be linked together.

One thing to note though; If you link product to customer and currency to customer product and currency are going to end up linked through customer, which is where the duplicate table would come in again.

There should be a solution, it's judt complicated to think of when I can't see what you're working with. Sorry!