swpui
asked on
Create SQL Table and relationship
I have 2 tables:
(1)Customer Table: Cust_Name, Cust_Address, Cust_age…….
Order Table: Cust_Name, Ord_Qty, Ord_Price……..
When I filled Cust_name in Customer Table, how to auto fill the Cust_Name in Order table as well?
(2)If I create Employee table with Employee_ID, Employee_Name_Address, and Skill. An employee may
have more than one skill. Each employee may work in one or more work centers. A
work center must have at least one employee working in that center; but may have
any number of employees.
How to link many skill to the Employee table, create another table or just put skill1, skill2 as attribute?
I already have work center table consist of workcenterID, workcenter_location……..how
(1)Customer Table: Cust_Name, Cust_Address, Cust_age…….
Order Table: Cust_Name, Ord_Qty, Ord_Price……..
When I filled Cust_name in Customer Table, how to auto fill the Cust_Name in Order table as well?
(2)If I create Employee table with Employee_ID, Employee_Name_Address, and Skill. An employee may
have more than one skill. Each employee may work in one or more work centers. A
work center must have at least one employee working in that center; but may have
any number of employees.
How to link many skill to the Employee table, create another table or just put skill1, skill2 as attribute?
I already have work center table consist of workcenterID, workcenter_location……..how
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so is the attached ER correct for relatioship between Employee & Skill?
Doc1.docx
Doc1.docx
ASKER
Let say I have sales territories for customers. Each customer may do
business in any number of these sales territories or may not do business in any
territory . A sales territory has one-to-many customers. The identifier for a sales
territory is Territory_ID and an attribute of a Territory_Name.
Territory table: TerritoryID Int Primary Key, TerritoryName nvarchar(50)
customer table: custID Int Primary Key, custName nvarchar(50)
custTerritory table: custterritoryID int Primary Key, TerritoryID Int FK, custID Int FK
ER would be:
Territory Tabel (1 to many) custTerritory table
customer table (0,1 or many) custTerritory table
How to draw relationship for "Each customer may do
business in any number of these sales territories or may not do business in any
territory?
business in any number of these sales territories or may not do business in any
territory . A sales territory has one-to-many customers. The identifier for a sales
territory is Territory_ID and an attribute of a Territory_Name.
Territory table: TerritoryID Int Primary Key, TerritoryName nvarchar(50)
customer table: custID Int Primary Key, custName nvarchar(50)
custTerritory table: custterritoryID int Primary Key, TerritoryID Int FK, custID Int FK
ER would be:
Territory Tabel (1 to many) custTerritory table
customer table (0,1 or many) custTerritory table
How to draw relationship for "Each customer may do
business in any number of these sales territories or may not do business in any
territory?
ASKER
My last comments nobody seems to reply , anyway I had tried it and understand now
http://www.asp.net/web-forms/videos/sql-2005
You need to understand designing relational databases, database normalisation and from what I can tell from your question, you need to understand how to write stored procedures to do the work for you.
With regards to the employee/skill relationship, my guess is that an employee can have many skills and a skill can belong to many people so this is a many to many relationship. As you cannot model this in a relational database directly you need to normalise it to give you this type of structure:
Employee --< EmployeeSkill >-- Skill
That way you have a skill table that links to an Employee via an EmployeeSkill table.