Designing a database which can track stock movements

Question

I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the Customers. We need to keep track of all the Control Units whether they are assigned to the Machines, they are sent to Customers as spares, they have being returned back to the Supplier for repair or in stock. I am trying to setup tables to record of the history of the Locations of Machines which can be assigned to a Customer but then the same machine can be moved/reassigned to another Customer. The Machine has a Serial Number. Also need to record the history of the Locations of a Control Unit which can be assigned to a Machine which can then be moved/reassigned to another Machine. A Control Unit can also be sent to a Customer as a backup unit in case of failure or it can be sent to the manufacturer for repair. After the Control Unit comes back from the Manufacturers it is put back in stock and at some stage it can be reassigned to another Machine. The Control Unit also has a Serial Number.

I have attached an ER Diagram of what I think might be a suitable solution. The ERD diagram shows the link to MachineID, CustomerID and ManufacturerID in the TBL_ControlUnitLocations table. The problem I see is that when you select which link you want, the other two will need to be made NULL. When you view the history on a particular Control Unit you will end up with two fields always being blank.

One option is not to show the MachineID, CustomerID and ManufacturerID fields on the main form and only show the Location field. Is it possible to have another form which pops up and gives you the option to choice which one of the MachineID, CustomerID and ManufacturerID fields you wish to link to and then assign the link to the Location field and then close the popup form. So all you see then is the Location field.

How would you implemented the main form and the popup form to record the association?

Answer 1

What you're looking for is a VIEW. Here is a link for more description.

https://msdn.microsoft.com/en-us/library/office/ff836312.aspx

This is what it will look like, and you can create a form around the view.

CREATE VIEW vw_cust_mach_loc
AS
SELECT cust.CustomerName,
   cust.CustomerAddress,
   cust.SuburbTownCity,
   cust.State,
   cust.PostCode,
   cust.Country,
   cust.FirstName,
   cust.SurnameName,
   cust.PhoneNumber,
   cust.MobileNumber,
   cust.EmailAddress,
   cust.Notes,
   m_lo.CustomerReference,
   m_lo.TimeStamp,
   m_lo.StatusID,
   m_lo.OriginalCustomer,
   m_lo.Notes,
   mach.MachineSerialNumber,
   mach.MachineModeID,
   mach.MachineOtherFields,
   mach.Notes
FROM TBL_Customers cust
   INNER JOIN TBL_MachineLocations m_lo
       ON cust.CustomerID = m_lo.CurrentCustomerID
   INNER JOIN TBL_Machines mach
       ON m_lo.MachineID = mach.MachineID;

CREATE VIEW vw_cust_mach_sup_ctrl_units
AS
SELECT cust.CustomerName,
       cust.CustomerAddress,
       cust.SuburbTownCity,
       cust.State,
       cust.PostCode,
       cust.Country,
       cust.FirstName,
       cust.SurnameName,
       cust.PhoneNumber,
       cust.MobileNumber,
       cust.EmailAddress,
       cust.Notes,
       mach.MachineSerialNumber,
       mach.MachineModeID,
       mach.MachineOtherFields,
       mach.Notes,
       culs.Location,
       culs.Reference,
       culs.TimeStamp,
       culs.OriginalMachine,
       culs.OtherLocationID,
       culs.StatusID,
       culs.Notes,
       supl.SupplierName,
       supl.ContactFirstName,
       supl.ContactLastName,
       supl.ContactTitle,
       supl.Address,
       supl.City,
       supl.Region,
       supl.PostalCode,
       supl.Country,
       supl.PhoneNumber,
       supl.MobileNumber,
       supl.Fax,
       supl.[E-mail Address],
       supl.HomePage,
       supl.Notes,
       cuts.ControlUnitSerielNumber,
       cuts.ControlUnitBarCodeID,
       cuts.ControlUnitStatusID,
       cuts.TimeStamp,
       cuts.Notes
FROM TBL_Customers cust
     INNER JOIN TBL_ControlUnitLocations culs
         ON cust.CustomersID = culs.CustomerID
     INNER JOIN TBL_Machines mach
         ON culs.MachineID = mach.MachineID
     INNER JOIN TBL_Suppliers supl
         ON culs.ManufactureID = supl.SupplierID
     INNER JOIN TBL_ControlUnits cuts
         ON culs.ControlUnitID = cuts.ControlUnitIDMachineID;

You can select from it like a table.

SELECT * FROM vw_cust_mach_loc;
SELECT * FROM vw_cust_mach_sup_ctrl_units;

Example using specific columns instead of *:

SELECT
    Location,
    Reference,
    TimeStamp,
    OriginalMachine,
    OtherLocationID,
    StatusID,
    Notes,
    SupplierName,
    ContactFirstName,
    ContactLastName,
    ContactTitle,
    Address,
    City,
    Region,
    PostalCode,
    Country,
    PhoneNumber,
    MobileNumber,
    Fax,
    [E-mail Address],
    HomePage,
    Notes,
    ControlUnitSerielNumber,
    ControlUnitBarCodeID,
    ControlUnitStatusID,
    TimeStamp,
    Notes
FROM vw_cust_mach_sup_ctrl_units