Dreamweaver - many to many relationship

Author

many to many relationship

Rajiv Vaishnav

2006-02-24, 10:15 am

have two tables :

master worker
machine1_operator worker_id
machine2_operator worker_name
machine3_operator
machine4_operator
machine5_operator
machine_name
machine_brand
etc

Now the scenerio is in master table only the value of worker_id is inserted,
now when i relate this two field, i want the actual names of the workers
from worker table so there is only one worker table from which it should
pick the respective names of worker according to ids in master table.

How to do this ?

please help.

Kristian Todd

2006-02-24, 10:15 am

There is no worker_id number in the master table but if there was one you
could use:-

Select *

From Master

Inner Join Worker
On Worker_id = Master.worker_ID

Where Master.Worker_if = 'Paramater'

HTH

"Rajiv Vaishnav" <rajiv@sonil.co.in> wrote in message
news:dtmbf1$38g$1@forums.macromedia.com...
> have two tables :
>
> master worker
> machine1_operator worker_id
> machine2_operator worker_name
> machine3_operator
> machine4_operator
> machine5_operator
> machine_name
> machine_brand
> etc
>
> Now the scenerio is in master table only the value of worker_id is
> inserted,
> now when i relate this two field, i want the actual names of the workers
> from worker table so there is only one worker table from which it should
> pick the respective names of worker according to ids in master table.
>
> How to do this ?
>
> please help.
>
>
>

PeteC

2006-02-24, 10:15 am

Rajiv Vaishnav wrote:
> have two tables :
>
> master worker
> machine1_operator worker_id
> machine2_operator worker_name
> machine3_operator
> machine4_operator
> machine5_operator
> machine_name
> machine_brand
> etc
>
> Now the scenerio is in master table only the value of worker_id is
> inserted, now when i relate this two field, i want the actual names
> of the workers from worker table so there is only one worker table
> from which it should pick the respective names of worker according to
> ids in master table.
> How to do this ?

Am I right in saying that you have machines and workers; a machine may have
several operators(workers), and each worker may operate several machines.

If so, you do have a true many-many relationship. To handle this, you need a
'resolution entity' - a third table. In your example above, you've tried to
get around it by assigning up to five operators per machine - but if you
only have one operator on a machine, you're still assigning five fields; and
what happens when operator number 6 comes along?

If you use three tables, your structure would be more like this;

table: MACHINE_TABLE
machine_id (unique primary key)
machine_name
machine_brand

table: WORKER
worker_id (unique primary key)
worker_name

table: OPERATORS
machine_id
worker_id
(the primary key on this table is both fields - and no other data is
required)

So to add workers to a machine, you create a record in the OPERATORS table
with just the keys. To remove a worker from a machine, you remove the record
from the OPERATORS table

Some sample data;

MACHINE
1, "Big Drill", "Makita"
2, "Press Brake", "Weller"
3, "Laser Cutter", "Bystronic"

WORKER
10, "Fred Bloggs"
11, "Arthur Smith"

OPERATORS
1, 10
2, 10
2, 11
3, 10

In this case, Fred Bloggs can operate all three machines, but Arthur Smith
can only operate the Press Brake. (I used a key base of 10 for the workers
so that the data in OPERATORS is easier to analyse)

HTH,

Pete.
--
Peter Connolly
http://www.acutecomputing.co.uk
Derby
UK
Skype ID: acutecomputing

Rajiv Vaishnav

2006-02-24, 10:15 am

Dear Kristian,

As you told "there is no worker_id number in master table but
machine1_operator, machine2_operator itself is a worker id where the ids of
the workers is inserted and i want the respective names to be seen which is
fetched from worker table.

"Kristian Todd" <kristiantodd@XXXXXXXXXX> wrote in message
news:dtmib6$dbh$1@forums.macromedia.com...
> There is no worker_id number in the master table but if there was one you
> could use:-
>
> Select *
>
> From Master
>
> Inner Join Worker
> On Worker_id = Master.worker_ID
>
> Where Master.Worker_if = 'Paramater'
>
> HTH
>
>
>
> "Rajiv Vaishnav" <rajiv@sonil.co.in> wrote in message
> news:dtmbf1$38g$1@forums.macromedia.com...
>
>

Rajiv Vaishnav

2006-02-24, 10:15 am

Dear Pete,

Here is my actual tables :

http://img506.imageshack.us/img506/...tionship3bi.gif

I have explained all the stuff in the image itself.

"PeteC" <noemailrequired@nospamrequired.co.uk> wrote in message
news:dtmis8$e2c$1@forums.macromedia.com...
> Rajiv Vaishnav wrote:
>
> Am I right in saying that you have machines and workers; a machine may
> have several operators(workers), and each worker may operate several
> machines.
>
> If so, you do have a true many-many relationship. To handle this, you need
> a 'resolution entity' - a third table. In your example above, you've tried
> to get around it by assigning up to five operators per machine - but if
> you only have one operator on a machine, you're still assigning five
> fields; and what happens when operator number 6 comes along?
>
> If you use three tables, your structure would be more like this;
>
> table: MACHINE_TABLE
> machine_id (unique primary key)
> machine_name
> machine_brand
>
> table: WORKER
> worker_id (unique primary key)
> worker_name
>
> table: OPERATORS
> machine_id
> worker_id
> (the primary key on this table is both fields - and no other data is
> required)
>
> So to add workers to a machine, you create a record in the OPERATORS table
> with just the keys. To remove a worker from a machine, you remove the
> record from the OPERATORS table
>
> Some sample data;
>
> MACHINE
> 1, "Big Drill", "Makita"
> 2, "Press Brake", "Weller"
> 3, "Laser Cutter", "Bystronic"
>
> WORKER
> 10, "Fred Bloggs"
> 11, "Arthur Smith"
>
> OPERATORS
> 1, 10
> 2, 10
> 2, 11
> 3, 10
>
> In this case, Fred Bloggs can operate all three machines, but Arthur Smith
> can only operate the Press Brake. (I used a key base of 10 for the workers
> so that the data in OPERATORS is easier to analyse)
>
> HTH,
>
> Pete.
> --
> Peter Connolly
> http://www.acutecomputing.co.uk
> Derby
> UK
> Skype ID: acutecomputing
>

PeteC

2006-02-24, 10:15 am

Rajiv Vaishnav wrote:
> Dear Pete,
>
> Here is my actual tables :
>
> http://img506.imageshack.us/img506/...tionship3bi.gif
>
> I have explained all the stuff in the image itself.

Hi Rajiv,

My previous answer still stands as (imo) as the start of a better solution.
Your design shows an unnormalised table, which has lots of repeating data
(e.g insp_insp1, insp_insp2, insp_insp3, insp_insp4 - what happens if a
fifth inspection is needed?).

There are arguments for and against normalisation, with the best argument
being to do what's best for the application in hand - considering the
quantity of data involved, the actual requirements of the system and the
potential growth of the system over a period of time. If you make yourself
aware of the problems and advantages of normalisation, you'll be able to
make a proper judgement.

I notice that you're using Access. You could do what you want to do using a
query to pull in the information. I'd advise against using the 'Lookup'
feature in table design, as it will cause lots of problems when you come to
produce reports.

HTH,

Pete.
--
Peter Connolly
http://www.acutecomputing.co.uk
Derby
UK
Skype ID: acutecomputing

Rajiv Vaishnav

2006-02-24, 10:15 am

Dear Pete,

Dont worry about adding more fields like your question "what happens if
fifth inspection is needed ? " at least guide me how to solve the present
scenerio, and i am confused about creating third table you have seen i have
two tables and u have seen all the fileds, the thing is there are various
operators involved in various processes, i just want to pull their names in
asp page from worker table.

plssssssssss help.

rajiv
"PeteC" <noemailrequired@nospamrequired.co.uk> wrote in message
news:dtmn0f$jcb$1@forums.macromedia.com...
> Rajiv Vaishnav wrote:
>
> Hi Rajiv,
>
> My previous answer still stands as (imo) as the start of a better
> solution. Your design shows an unnormalised table, which has lots of
> repeating data (e.g insp_insp1, insp_insp2, insp_insp3, insp_insp4 - what
> happens if a fifth inspection is needed?).
>
> There are arguments for and against normalisation, with the best argument
> being to do what's best for the application in hand - considering the
> quantity of data involved, the actual requirements of the system and the
> potential growth of the system over a period of time. If you make yourself
> aware of the problems and advantages of normalisation, you'll be able to
> make a proper judgement.
>
> I notice that you're using Access. You could do what you want to do using
> a query to pull in the information. I'd advise against using the 'Lookup'
> feature in table design, as it will cause lots of problems when you come
> to produce reports.
>
> HTH,
>
> Pete.
> --
> Peter Connolly
> http://www.acutecomputing.co.uk
> Derby
> UK
> Skype ID: acutecomputing
>

Joe Makowiec

2006-02-24, 10:15 am

On 24 Feb 2006 in macromedia.dreamweaver, Rajiv Vaishnav wrote:

> Dont worry about adding more fields like your question "what happens
> if fifth inspection is needed ? " at least guide me how to solve the
> present scenerio, and i am confused about creating third table you
> have seen i have two tables and u have seen all the fileds, the
> thing is there are various operators involved in various processes,
> i just want to pull their names in asp page from worker table.

Rajiv, he did answer the question. Reread
Message-ID:<dtmis8$e2c$1@forums.macromedia.com>

What you'd do is:

SELECT MACHINE_TABLE.machine_name, MACHINE_TABLE.machine_brand,
WORKERS.worker_name
FROM MACHINE_TABLE JOIN OPERATORS on MACHINE_TABLE.machine_id =
OPERATORS.machine_id JOIN WORKERS ON OPERATORS.worker_id =
WORKERS.worker_id

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

Rajiv Vaishnav

2006-02-24, 10:15 am

pls visit http://img506.imageshack.us/img506/...tionship3bi.gif

here is my actual tables, pls guide me how to write the query.

regards
Rajiv

PeteC

2006-02-24, 10:16 pm

Rajiv Vaishnav wrote:
> Dear Pete,
>
> Dont worry about adding more fields like your question "what happens
> if fifth inspection is needed ? " at least guide me how to solve the
> present scenerio, and i am confused about creating third table you
> have seen i have two tables and u have seen all the fileds, the thing
> is there are various operators involved in various processes, i just
> want to pull their names in asp page from worker table.
>
> plssssssssss help.

Sorry Rajuv, I *can't* help any further. You're heading down a dead-end road
which is only going to cause you lots of problems. As bregent says, there is
a solution, but it's messy, clunky and will cause lots of problems.

You really will make much faster progress if you take a step backwards and
learn more about how a relational database wants to work, and redesign your
database to accomodate this.

Have a read of http://www.databasejournal.com/sqle...cle.php/1469521 for
a good run down of what a relational database can do for you and what
normalisation is used for.

There is also a newsgroup (news://microsoft.public.access) where you'll be
able to gain more specific help.

HTH,

Pete.
--
Peter Connolly
http://www.acutecomputing.co.uk
Derby
UK
Skype ID: acutecomputing

bregent

2006-02-24, 10:16 pm

>here is my actual tables, pls guide me how to write the query.

You will need to create an alias of the Worker table for every instance
referenced in the Master table, then join each of those in the query. This is
going to be very ugly and probably perform terribly.

Your database design is bad. You really need to listed to the others and
create a relation table or hire someone that knows more about database design.
You are currently going down a very bumpy road and it will only get worse.