Normalisation clarification

Question

Good day all

I am having a little problem here (bear in mind i am new to this) . I am building a very very small inventory system in principle. I have attempted to normalise my table. I am having a little problem that i will show, its when i get to the 3NF. This is the process

UNF

    Product (productID, productName, productBrand, productQuantityinstock, 
            productQuantitySold, productCost, adminstaffID, adminstaffName,
             adminstaffUserName  adminstaffPassword, productcategoryID, 
            productcategoryName, { customerID, customerName,      
           customerAddress, customerContact, serviceID,
           serviceType,serviceCost, serviceStatus, 
           serviceDetails, techstaffID, tsUsername, 
           techstaffPassword } )

INF

    Product (productID, productName, productBrand, productQuantityinstock, 
            productQuantitySold, productCost, adminstaffID, adminstaffName,
            adminstaffUserName  adminstaffPassword, productcategoryID, 
            productcategoryName)


    Product_Customer (productID, customerID, customerName, customerAddress, 
                      customerContact, serviceID, serviceType,       
                       serviceCost,serviceStatus, 
                       serviceDetails, techstaffID, tsUsername, 
                      techstaffPassword)

2NF

    Product (productID, productName, productBrand, productQuantityinstock, 
             productQuantitySold, productCost, adminstaffID, adminstaffName,
             adminstaffUserName  adminstaffPassword, productcategoryID, 
             productcategoryName)

    Product_Customer (productID*, customerID*)

    Customer (customerID, customerName, customerAddress, customerContact, 
              serviceID, serviceType, serviceCost, serviceStatus,    
              serviceDetails, techstaffID, tsUsername, 
              techstaffPassword)

3NF

     Product (productID, productName, productBrand, productQuantityinstock, 
              productQuantitySold, productCost, productcategoryID*,  
              adminstaffID* ,)

     Admin Staff (adminstaffID, adminstaffName, adminstaffUserName   
                  adminstaffPassword,)

     Product Category (productcategoryID, productcategoryName,)

     Product_Customer (productID*, customerID*)

     Customer ( customerID, customerName, customerAddress, customerContact) 

     Tech Staff ( techstaffID, techstaffUsername, techstaffPassword)

     Service (serviceID, serviceType, serviceCost, serviceStatus,  
              serviceDetails, customerID*, techstaffID*)

Above is what I did but i was advised that the in the 3NF form the customerID and techstaffID in the SERVICE table must go into the CUSTOMER table.

The system is designed so a customer may have one or many services and a tech staff will carry out one or many services. So i know that the foreign key always goes on the many side of a relationship so if

Customer to service will be 1-M with the many multiplicity on the service side

and

Techstaff to service will be 1 - M with the many multiplicity on the service side as well

I have attached an image of my ERD. Any assistance will be greatly appreciated as to where these two foreign keys should go.

link to ERD, cant post images as yet

Answer 1

I was advised that the in the 3NF form the customerID and techstaffID in the SERVICE table must go into the CUSTOMER table.

Don't follow this advice. Your 3NF is fine. A Service call will involve a Customer and a TechStaff, so having their IDs as foreign keys in the Service table is correct. If you follow the advice, it will mean a customer is connected to a single tech staff for all his/her service requests.

Another suggestion that I have: why not combine the TechStaff and AdminStaff into one Staff table, like this:

Staff
(
    StaffID,
    StaffFullName,
    StaffUsername,
    StaffPassword,
    StaffTypeID -- use this column to determine if a staff is tech or admin
)