Hi all
This post is long so as to give the maximum of info, but isn't that complicated...
I need to change the structure of an SQL Server database and need to write a script to populate a new table in the database.
This has to be a script and will be used at the client site to deploy these changes automatically IYSWIM..
Current system:
We have a table called "machine" (names have been changed to protect teh innocent ):
Machine
-------
Machine reference (primary key)
Machine width
Machine height
Machine depth
These machines are used by Production lines, and so the Machine reference was also a foreign key in the "Production Process" table:
Production Process
------------------
Production process reference (primary key)
Production process name
Production process characteristic_1
Production process characteristic_2
Machine reference (foreign key)
Factory reference (foreign key)
As you can see Production process also has a Factory reference (foreign key) field. - A Production process belongs to a factory.
Factory
------------------
Factory reference (primary key)
Factory name
So the entity diagram:
--------- -------------------- ---------
!Machine! !Production Process! !Factory!
! !-----------! !<-------! !
!--------! !------------------! !-------!
So important thing here is that the configuration of the machine is the same across all factories. ie each machine has one configuration and one configuration only and is therefore the same for the production processes in all the factories.
New system:
In new system, each factory can configure each machine locally. When a new machine is added to the machine table (by the administrator), the default characteristics are typed in, but now, each factory has to have the option of configuring each machine.
So now we have:
Machine
-------
Machine reference (primary key)
Default Machine width
Default Machine height
Default Machine depth
Factory configured machine
--------------------------
Machine reference (primary key)
Factory reference (primary key)
factory-specific Machine width
factory-specific Machine height
factory-specific Machine depth
Production Process
------------------
Production process reference (primary key)
Production process name
Production process characteristic_1
Production process characteristic_2
Machine reference (foreign key)
Factory reference (foreign key)
Factory
------------------
Factory reference (primary key)
Factory name
So the new entity diagram:
--------- -------------------- ---------
!Machine} !Factory-configured! !Factory!
! !--------------->! Machine !<----------! !
!-------! !------------------! !-------!
!
!
!
!
\!/
--------------------
!Production Process!
--------------------
The script I need:
I need to write a script which will populate the new "Factory configured machine" table. So for every machine reference it needs to add a record for every Factory reference. The factory-specific Machine width, factory-specific Machine height and factory-specific Machine depth fields should be initialised with the default values from the machine table (the factory managers can then go back and update these values with whatever they want.
TIA for any help
