Hi All,

I have some concern regarding the ER Diagram. I have the following scenario: We need to keep all the information regarding the agent like Name, Address, Phone No and also his monthly salary, commission paid. Now I have created an entity Agent and defined attributes which are necessary and required.

My question is should I have a separate entity for maintaining the Agents Salary ? The reason why I am asking is like, the salary may change sometime say the agent might get an increment. Now because of that, if the agent salary is kept in the Agent Entity and I need to update the whole table when there is increase in the salary of the agent. I guess it is not a good practice to fire a query to update a single Attribute. Also if I have 100K records it will take a lot of time to update.

From the knowledge I have it is always good to have a separate Table for the attribute whose values keeps changing in my case it is "Salary".

So can you suggest me which is the correct way .. Keeping all attributes in one table or having a separate table when we have such scenario ?

I will really appreciate your reply and guidance.

A person's name, address, and phone number can change too. Just because something might change isn't a reason to put it into its own table.

If agents of a certain type all get the same salary and that salary will go up for all of the agents of that type then you would want to put salary into a table with agent type and add type to the agent table. Otherwise leave the salary in the agent table.

I agree to your point. But, say all the agents have different salary and at the end of every three month they get an increment then should I have a separate table for that ?

Leave the salary in the agent table. Breaking the salary out won't help because you still have to update the same number of records (however many changed) and the same number of columns (one). The machine and programmer effort to do the update will be the same either way.

However, if the salary is left in the agents table and the agent gets a new value (which is upddated), then you will not be able to see the old value.

I expect that it's largely a matter of knowing the end user requiements.

If you need to see and apply history data, then you need to spin off salary into a separate table with another column called effective data. Further, only one attribute may change.

End

@ AnanthaP

Hello Sir,

I really appreciate your reply. So you mean if for an attribute we need to store historical information we should always have an separate table for that entity right ?

in what other information do I need to make a separate entity for an attribute ??

Thanks for your time and consideration

Repeating groups ,

Data dependant on part of the primary key only,

Data not dependant on the primary key alone.

End

If you need the history of an entity of its attribute then you need to devise a mechanism to appropriately store that entity/attribute. how you implement that is up to you

separate entity/separate table rarely makes sense, there are occasions when it does make sense but they are rare.

I could see some value is say recording an agents commission over time becuase it will vary period by period, it may be of interest, but not salaray