Tables are tied together by whichever field(s) you use to store their
"parent"'s reference.
For one second, imagine I am writing an inventory control program for
somebody like Wal-Mart or Target. Those businesses have so many locations
that they are divided into regions, each region will have multiple
warehouses, each region would also have multiple stores. Each store could
be within supply range of several warehouses. Each warehouse can supply
several stores.
That gives us the following relationships:
1 region : many warehouses
1 region : many stores
many stores : many warehouses
CREATE TABLE region (
ID int auto_increment,
Name varchar(20) primary key,
key(id)
)
CREATE TABLE warehouse (
ID int auto_increment,
region_id int not null,
Name varchar(50) primary key,
key(ID)
)
CREATE TABLE store (
ID int auto_increment,
region_id int not null,
Name varchar(50) primary key,
key(ID)
)
CREATE TABLE stores_warehouses (
store_id int not null,
warehouse_id int not null,
primary key (store_id, warehouse_id)
)
I left out a lot of other useful information (like addresses, phone
numbers, etc) but I think you see the basic structure. The warehouse
table relates to the region table through the field region_id. The store
table relates to the region field through the field region_id. The stores
and warehouses relate to each other through the table stores_warehouses
and their respective ID columns.
It appears to me that your tables are similar in organization but you
have:
1 member : many table1's (bad choice of table name. shouldn't this
be "member_language"?)
1 member : many table2's (maybe you could call this
"member_nationality" ?)
It seems that their common fact is that they both refer to the same
member. That's how their records are related, by their common member. The
other 3 tables are also contains lists of things that belong to the
member, right? Please post the unadulterated results of SHOW CREATE TABLE
for each of these 6 tables and we can help you formulate the query you
want to write.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein <[EMAIL PROTECTED]> wrote on 10/04/2004 03:46:49 PM:
> I've worked through some of this but still would like
> some opinions. Maybe it's not clear but I haven't
> received any responses.
>
> Basically to tie the records together I will use the
> recordID (auto incrementing) in every table where the
> records are written.
>
> Then I can grab everything out of that recordID.
>
>
>
> > I'm not sure , can't resolve in my mind if this is a
> > M2M or something else.
> >
> > I have 5 tables, users may enter multiple records in
> > each table. The only trick is (for me) is how to
> > tie
> > a unified record together across all of them.
> > I'll try to illustrate, and only use 2 tables to
> > keep
> > it brief.
> > Table1 - Bob has 3 records
> > 1st record - Spoken Language is Spanish
> > 2nd record - Spoken Language is French
> > 3rd record - Spoken Language is English
> >
> > Table 2 - Bob has 3 records
> > 1st record - I am Spanish
> > 2nd record - I am French
> > 3rd reocrd - I am English
> >
> > Okay the table strutures:
> > Table1 Table2
> > RecordID (int, autoinc) RecordID (int, autinc)
> > MemberID (int) MemberID (int)
> > Language (varchar) Nationality (varchar)
> >
> > I'm trying to say here is a record , that would form
> > the result of I am Bob, I speak English, I am
> > English
> >
> > I know, this probably sounds a bit weird :)
> > Best way I can come up with right now to illustrate.
> > If someone was searching through records, they would
> > say I found someone who is Spanish and yes, they are
> > Spanish. Not, I found someone who is Spanish and
> > they
> > speak Spanish , French and English.
> >
> > I considered (as this is part of a web site)
> > generating an ID and then passing it into each table
> > entry as the forms (that comprise the process) are
> > submitted. Just to clarify, 5 tables - 5 forms ,
> > all
> > part of 1 "web entry".
> >
> > Stuart
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
