Database Design

[victor64]

Hello,

I am having difficulties designing a database with data from table 1 referencing multiple data in table 2. For example:

Table 1 includes:

id Title

1 105MM

2 155MM


Table 2 includes:

id Country

1 BEL

2 CAN

3 FRA

4. ITA

How do I design both Tables in order to link Table 1 with multiple records in Table 2?.

For example when I select 105MM, I want to see all the records from Table 2 that pertains to 105MM. I am trying to avoid duplicating the coutrires in table 2 just to link to the id column in table 1.

Output Example:

105MM
- BEL
- CAN
- ITA

155MM
- BEL
- FRA
- ITA

I think I need to add other columns in Tables 1 and 2 because if I only use the ID columns from both tables, I will only link to table 2 where the ID columns match in both tables. I want to be able to see multiple records in table 2 that applies to a record in table 1. For example when selecting 105MM in table 1, I want to see
BEL, CAN,ITA from tablle. Those same countries may also be linked with other titles in table 1.

Thanks,

Victor

 

[InertiaM]

In summary, you need a third table. Each record in your third table is a 'permutation' record, which has a primary key (the ID), and then the IDs from the other tables to use for reference. For example, table 3 would be :-

ID       TABLE_1_ID     TABLE_2_ID
1        1              1                (ie 105MM, BEL)
2        1              2                (ie 105MM, CAN)
3        1              4                (ie 105MM, ITA)
4        2              1                (ie 155MM, BEL)
5        2              3                (ie 155MM, FRA)
6        2              4                (ie 155MM, ITA)

There are other ways to do it (eg binary code a field into table 1), but a cross-reference table is probably your best starting point.

 

[victor64]

Using the third table, can you please give me an example on how multiple records in table 2 would link with table 1?. I don't understand the purpose of the Table_IDs in table 3.

Thank you.

Victor

 

[InertiaM]

You dont have multiple records in table 2. Table 2 holds countries, which are unique records.

Tables 1 and 2 no longer link to each other at all - they link via table 3.

I dont know what your table 1 records relate to, so I'll have to use a different analogy.

Table 1
CAR_MAKE  DESCRIPTION
1         Audi
2         GM/Opel/Vauxhall
3         Ford

Table 2
CAR_COLOUR  DESCRIPTION
1           Red
2           Green
3           Blue


Table 3
ID     CAR_MAKE  CAR_COLOUR
3744   1         1              (ie a red Audi)
3745   1         2              (ie a green Audi)
3746   3         2              (ie a green Ford)
3747   3         3              (ie a blue Ford)

The ID records in table 3 are just random so as to not confuse the issue.

So, if you want to find all Fords, you search by CAR_MAKE=3 in Table 3. If you want all green cars, you search by CAR_COLOUR=2 in Table 3.

 

[victor64]

I understand it now. I'm using this database with crystal reports, for example will group by car_make from table 3 and should autamatically link to tables 1 and 2.

Thanks a million.

Victor

 

[victor64]

Problem with linking tables

Hello,

The approach given is not working when trying to link table1 and table 2 in crystal report. I'm only able to see the values for table 1, once I group by table 2, the report doesn't work. Any idea what's wrong?

Thanks,

Victor

 

[InertiaM]

Without seeing the query, no idea at all. Post the SQL used and it may help us to diagnose the problem

 

[victor64]

Problem with linking original tables with Link Table in Report

Hello,

Enclosed are the files I am working with. I am only able to group by the "Make " table, once I try to group by the "colour" table the report doesn't work. Can you please help me solve this problem. It seems like the link table which contains the permutation records is not working.

Thank you.

Victor

 

[InertiaM]

You shouldnt be grouping by the Make table. All your groupings should be on the link table. You should only be retrieving the descriptions from the other tables.

 

[cjard]

Victor, as a quick into to aggregate database queries:


When you GROUP BY, you specify a set of columns whose values are to be used as lookups in a dictionary. Any other columns that are not constants, can only be expressed in terms of an aggregate function such as SUM, COUNT, AVG. Picture it that the database will scan your result set, and set up many buckets, labelling each with the values found in that particular row's grouped columns. Then the entire row gets chucked in the bucket. After this collection phase is complete, the database visits every bucket, calculating the aggregate functions such as SUM, COUNT.. COUNT counts the number of rows in the bucket, SUM adds up the values of (whatever column you specified) in all the rows in the bucket. etc etc

If you group by MAKE of car, :

SELECT
make,
count(*)
...
GROUP BY make

And your database query without grouping returns 10 makes of car, each with 20 colours (200 records), you'll get 10 records, and the count(*) in each will be 20.

If you grouped by colour, there would be 20 records (remember there are 20 distinct colours) each having a count of 10

If you grouped by make and colour you'd get 200 records each having a count of 1


What you need to do in your mind is work out what you want to show as a key, and what you want to show as an aggregate.

If you want to group by multiple things, you either run several queries or use a database that understands grouping sets


As an illustration, if you group by make, colour, you are not "group by make (and do the counts) and then also as a separate operation, group by colour",
you are "group by make and colour together in one operation"

It's been a while since I used crystal, I'm not 100% sure that you can have 2 grouping sections grouping by different things as totally separate entitites. You might have to have 2 reports, and embed them in a third. one report groups by make, the other by colour The third simply acts as a container