Our company has its Departments And Services.
Now We are making it online.
Both have separate email list, phone numbers, and more.
Will I make one table and adds the field Type (Values: D or S).
Or make them separate.
Remember one thing If we merge them then Email And PhoneNumber Table will also me merge
other wise they will also separate.
What is better.
You should definitely separate these two tables!
Can a service belong to more than one department?
If the answer is yes, you will also need a third table that connects a department with one or more services. You can then use JOIN between these three tables to get a complete list of departments and services.
If the answer is no, then you can put a foreign key in the service table that links directly to a department.
Let us know if you need more assistance!
|||Yes you are right.
I secondly ask that PhoneNumber or Email Table are Same in nature for Department and Service both.
Will we make PhoneNumber or Email Table separate for PhoneNumber or Email Table.
Table list
Department
DepartmentEmail
Service
ServiceEmail
Is it right?
|||Well, that doesn't really make any sense if you only have "unique" departments and "unique" services stored, right?
The thing we are discussing here is called normalization, and it's all about reducing duplicate information. So if you don't have duplicate services or duplicate departments, then there's no need to break out email/phone in a separate table.
I think you you don't understand my point.
Multiple EmailAddress and PhoneNumbers per department or service is allowed.
I have a table Department.
And one Department can have multiple Email.
On the other end I have a table Service
Which also can have multiple Email.
-------
Department
-------
Id
Name
-------
-------
Service
-------
Id
Name
-------
Now I have to make Email Tables
And I think the table structure will.
-------
EmailDepartment
-------
Id
Address
DepartmentId
-------
-------
EmailService
-------
Id
Address
DepartmentId
-------
This one is O.k.
or you suggest something else.
|||Johram we can have multiple email addresses per department.
|||Johram we can have multiple email addresses per department.
||| Yes, if you can have multiple e-mail addresses then you are on the right track, dividing it further in email tables!
Sir, I think you understand all the story.
One last thing I am asking from you.
Bascially Question is that dividing Email Tables according to Department And Service Table
has been done by different ways.
One is that
Make separate Email Table for Department and Service Table Named EmailDepartment And EmailService.
I share this with my one professional.
He said not do that.
Make one Email table
And add a column name type in which take D for Department and S for Service
And add one more column of Id in which take the DepartmentId And ServiceId.
I am confused what to choose.
Waiting for your kind help.
|||
Problem is that if you want to use real constraints in the database (Foreign key checks) then this solution is not viable, because you need to express the relationship from the Email table to either Services or Departments, you cannot have both. There is no simple solution to this problem, unless you want to introduce an extra table above Services and Departments (think of it as inheritance), which you could then point your emails at.
However, for this purpose it might be sufficient to skip constraints and don't use foreign keys at all in the Email table. The solution your colleague suggested is a common solution. Database purists would not like it (since constraints cannot be used in a simple manner), but for this purpose it will work well.
Sidenote: It is possible (in some databases) to create insert rules to assure that the reference id inserted in the Email table refers to a valid Service or Department, even if you don't have a FK relationship.
Good luck!
|||Johram Please tell me what is good. Either 2 sparte email table is good or not.
I need the professional solution not the temporary.
If you have one in your mind.
Please reply. This will really help me.
Currently just becauase of that my work is stop.
||| Then I suggest you go for two Email tables. It is a more pure solution, and it will be easier for you to use. Good luck!
Yes you are right that is better.
But I have one more idea.
Assume.
We have Department Table with Id And Name Fields.
And Service Table with Id And Name Fields.
Make One Email Table with Id And EmailAddress Fields.
Now Make 2 Tables
DepartmentEmail with DepartmentId And EmailId Fields
And ServiceEmail with ServiceId And EmailId Fields
In comparision with this and old which you prefer in last post what do you prefer now?
Waiting for your reply.
|||Well, there's really no wrong or right in this case. The previous solution is simpler to implement (less joins). Although this suggestion (having an Email table) is probably the most normalized solution of all. But having a table with only one id and a string could be considered unnecessary.
Hmmm... how should I explain this. If you have a table with employees, something that you could build a relation to in many situations (salaries, bookings, reports etc) then it would be good to have a id, name table. That way, you could for instance have a combo of employees that you choose from, and so on.
But email is just a value, a property of an address. If you always have 1:1 (one to one) relations between Departments and its email and Services and its email, then I think you should have go for the previous solition (having the email field inline). But if you can see that the same email could be used in many situations then you could maybe consider having a separate Email table containing only id and name.
Hmmm... Wonder if this made things any clearer for you.
If I were you, I would settle with DepartmentEmail (DepartmentId, Email) rather than (DepartmentId, EmailId).
No comments:
Post a Comment