October 29, 2012 at 1:41 am
ok... 🙂
October 29, 2012 at 1:56 am
Actually your table design is not quite right.
I would set it up as
Create Table WorkPlace
(
WorkPlaceCode (PK)
,WRegn
,WorkplaceName
,NumberOfEmployees
,Address_Id
)
CREATE Table Employee
(
EmpNum (PK)
,EmpName
,WorkPlaceCode (FK)
)
CREATE Table Address
(
Address_Id (PK)
,Address1
,Address2
.....
)
This way you assign an employee to a workplace not a workplace to an Employee.
It also makes the joins Simpler, and everything else easier to do, for example in your original data if you wanted to know how many employees you had in a single workplace you would have to update multiple workplace rows everytime you added a new employee.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 29, 2012 at 2:30 am
Hi,
Below is the query i have done based on ur suggestion,
select WorkplaceLocation.[WorkplaceLocCode],WorkplaceDetails.[CompyRegnNum],WorkplaceDetails.[companyName],WorkplaceDetails.[NumOfEmployees],Employee.[EmpName],address.[OfficePhNum],address.[HomePhone],address.[Fax],address.[Address],address.[Address],address.homepage,address.[Email],WorkplaceDetails.[Notes1],WorkplaceDetails.[Notes2],WorkplaceDetails.[Notes3] from WorkplaceDetails
inner join Address on WorkplaceDetails.[CompyAddress_id]=address.[Address_id]
inner join Employee on WorkplaceLocation.[EmpNumber]= Employee.[EmpNum]
inner join WorkplaceLocation on WorkplaceDetails.[CompyRegnNum]=WorkplaceLocation.[CRegNum];
Beow is the output i got
WorkplaceLocCode CompyRegnNumcompanyName NumOfEmployees EmpName OfficePhNum
123 123 Leyland Motors1001 Prashant 29876585
I have curtailed few output coulumns.
Note: I have done normalization based on earlier soultion u suggested i.e normalization, not on latest change. 🙂
October 30, 2012 at 9:10 am
Jason-299789 (10/29/2012)
Actually your table design is not quite right.I would set it up as
Create Table WorkPlace
(
WorkPlaceCode (PK)
,WRegn
,WorkplaceName
,NumberOfEmployees
,Address_Id
)
CREATE Table Employee
(
EmpNum (PK)
,EmpName
,WorkPlaceCode (FK)
)
CREATE Table Address
(
Address_Id (PK)
,Address1
,Address2
.....
)
This way you assign an employee to a workplace not a workplace to an Employee.
It also makes the joins Simpler, and everything else easier to do, for example in your original data if you wanted to know how many employees you had in a single workplace you would have to update multiple workplace rows everytime you added a new employee.
I was just about to post the same change in table structure as you, but I would even go as far as to delete the NumOfEmployees... I'm assuming this is a count of all the employees linked to a company? So what, you going to update this value everytime you insert and delete a record?
I would just count this value with a GROUP BY everytime you query the table. It makes more sense to do it like that in my opinion.
October 30, 2012 at 9:38 am
Its a valid point about the number of Employees, and you could do a count against each employee for a given workplace.
It would depend on how frequently this number was being retrieved, if its very offrequent (100 times a day), then a count of the employees by workplacecode might actually be worse than the update everytime an employee is added.
Similarly if its a very infrequent request (one a month) then the count on the employees table wont be that much of an overhead.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply