Solved

Create SQL Table and relationship

Posted on 2012-04-03
5
288 Views
Last Modified: 2012-04-16
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 to link many employees to 1 work center?
0
Comment
Question by:swpui
  • 3
5 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37799894
I think you need to do a lot of research on relational databases.

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.
0
 
LVL 5

Accepted Solution

by:
hafeezmca earned 500 total points
ID: 37800109
HI,
Hope you are using Sql server/Ms access as the database. If not the structure will remain same but the datafield type will change.

Regarding your first question (Customer & Orders) create table with the following structure:

As you can get customer with same names so its not appropriate to have customer name as primary key. So I have added customerId as the Primary key.

Customer table: CustomerId Int Primary Key, Cust_Name nvarchar2(100), Cust_Address nvarchar2(100), Cust_age int …….

Order Table: CustomerId Int Foreign Key,  Ord_Qty int, Ord_Price decimal

You can create a form in any language.
The idea is to save the master record first ie. the customer table and get the customerId of the record inserted and then insert the data into Order table.


For your second query:

Create tables in the following structure:

Skills table: SkillId Int Primary Key, SkillName navarchar2
WorkCenter table: WorkCenterId Int Primary key, WorkCenterName nvarchar2
Employee table: Empid Int Primary Key, EmployeeName nvarchar2, Age int ....
EmployeeSkills table: EmpSkillId Int Primary Key, Empid Int Foreign Key, SkillId int Foreign Key
EmployeeWorkCenter table: EmpWorkcenterId Int Primary Key, Empid Int Foreign Key, WorkStationId int Foreign Key

This structure will help you to acheive your task.
You have to be good in programming to create master & details relationship pages.
0
 

Author Comment

by:swpui
ID: 37804679
so is the attached ER correct for relatioship between Employee & Skill?
Doc1.docx
0
 

Author Comment

by:swpui
ID: 37804691
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?
0
 

Author Closing Comment

by:swpui
ID: 37850478
My last comments nobody seems to reply , anyway I had tried it and understand now
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Group by correlation 4 55
if and else in stored procedure 19 42
format nvarchar field as mm/dd/yyyy 4 61
Problem with SqlConnection 4 159
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now