Should I use a separate table?

[Lozzy_uk]

Hi,

I want a relationship between 2 tables (I think) where Table 1 has an item which refers to multiple (but not all) items in another - and also not always to the same number of items in table2.

So my question is do I have to have a linking field in table 1 for each and every component for the item with the most number of references to table 2 or is there a better way?

e.g
Table1 = drink & Table2=components
in Table1 White Coffee with sugar links to Coffee, Milk, Sugar in table 2
in Table 1 Tea no sugar links to Tea, milk in table 2
in Table 1 Black coffee no sugar links to coffee in table2 only

in the above example would I need table 1 to have 3 linking fields to table 2 to allow for white coffee with sugar?

Bearing in mind Table 2 is likely to have 20 or so components, what’s the best way to set up this relationship please?

I hope I’ve been clear, as I’m obviously not an expert

thanks
Lozzy_uk

[EasyTrieve]

Edited your Q. I could see that you wanted a line feed after a few things, but it was not working. The text gets wrapped. See the preview. So I added two blank spaces after lines where you want a line feed, but not a paragraph break, e.g. after “in Table1 White Coffee with sugar links to Coffee, Milk, Sugar in table 2”

[Ratslinger]

Hello,

What you are describing here is a many-to-many relationship. This means that multiple items in table 1 can be associated with multiple items in table 2. Tea can have milk & sugar but coffee can also have milk and/or sugar.

To accomplish this you would need a third table. One which associates table 1 items with table 2 items. It need only consist of three fields - Unique primary key (auto-increment field), table 1 primary key data and table 2 primary key data. When you want to know the items in table 2 which comprise table 1 you need to access through this intermediate table.

Table 1

Primary key       Name

01                      Tea with milk
02                      Tea with sugar and milk
03                      Coffee with sugar


Table 2

Primary key      Name

01                      Tea
02                      Coffee
03                      Milk
04                      Sugar

Intermediate Table

Primary key      Table1 key      Table2 key

01                            01                  01
02                            01                  03
03                            02                  01
04                            02                  04
05                            02                  03
06                            03                  02
07                            03                  04

In a one-to-many relationship you would only need a link in table 2 back to table 1:

Table 1

Primary key            Name

01                           Beverages
02                           Vegetables

Table 2

Primary Key          Table1 key         Name

01                          01                      Coffee
02                          01                      Tea
03                          01                      Milk
04                          02                      Peas
05                          02                      Carrots
06                          02                      Corn

[Lozzy_uk]

My only worry is that (in your first example) as the number of component items in Table2 gets larger, the possible permutations in table1 might get enormous. But I guess if it’s still the most efficient way to do it?
regards
Lozzy_uk

[Ratslinger]

Table size is not a concern in most cases. The default HSQLDB v1.8 embedded DB has been known to be a problem with large amounts of data and the zipping of files. This is why the switch is in process to Firebird 3. Most using HSQL are recommended to at least switch to a split DB (one where data is outside of the .odb).

What may be more of a problem is locating the necessary items to link to. In this case you may want to consider list/combo boxes (another topic/question).

[Lozzy_uk]

Brilliant, thanks

[Ratslinger]

@Lozzy_uk - You are quite welcome.

In the future, please respond with a comment. Answers should be used only to respond to the original question. Of course you can also answer your own question.

[Lozzy_uk]

my apologies - I did realise after and comment on your answer though