I'm sure that this is probably 'Databases 101', but I can't seem to figure it out.
So, I'm setting up a database in Access - that I will want to move to MySQL, and I currently have 4 tables, set up as so:
Table1 - ID* - itemName - etc. Table2 - ID* - table1ID - subitemName - subitemDescription - table3ID1 - table3ID2 - table3ID3 - table4ID1 - table4ID2 - table4ID3 - table4ID4 Table3 - ID* - itemName Table4 - ID* - itemName
You see, the items in Table2 are associated with multiple items from both Table3 and Table4. For instance, 1 item in Table2 may be associated with 3 items from Table3 (and therefore, I'm assuming, 3 unique ID's), and 4 items from Table4 (and, again, 4 unique ID's).
So, I type the unique ID* value into each field, and create relationships from the Table3 ID* field to each table3ID field on Table2 (and same for Table4), yet when I expand the items on Table3 or Table4, I get nothing. However, if I only link the ID* to one of the table#ID fields, then expanding the item on its respective table shows all the items referencing it on Table2.
Does anyone know what I'm doing wrong/not doing right/generally effing up? I'd greatly appreciate any help.