This is a pretty common question and the best way to handle this is to ask yourself the following question:
Will I ever need to reuse this data across another application?
If the answer is yes then you should definitely design your table so that it is separate from the actual application. Let me give you an example. Assume you will be writing an application to log helpdesk tickets for your company's employees. One thing you notice is that employees are already stored in a common database called AcmeEmployees.
Your goal as a programmer / dba is to avoid duplication, administration, and typographical errors. So you only want to administer data at most once. It makes sense for your helpdesk application to reuse employee information that is stored in another database.
If you write a stored procedure and you want to access this employee data you simply do:
SELECT EmpFullName FROM AcmeEmployees.dbo.Employees
That is databaseName.ownder.tableName. So yes design with the idea in mind that you want to reuse common data. Here are some benefits to doing this:
- Inserts / Updates are done once and only once
- Little to no typographical errors (if it's done correctly once you never have to worry)
- Any change to the source data is automatically seen across your entire domain (all applications that use this common data)
- Reusability means less people doing clerical type work and more time to look at other issues at hand.
- Fields such as Status can impact an application, for example, in the helpdesk system I mentioned above if an employee decides to leave the company setting that employee = inactive will ensure that employee is no longer able to log tickets in the help desk system. It is a good means to keep old data out of the new systems
- And the best one, it just makes sense, in my workplace we have so many different applications but the nice thing is we have a set of common data tables. One of those is employees. I would hate it if we had to re-create an employee and set a first name, last name, middle initial, etc. every time we got a new employee and we had to insert that employee in each app.
Let's continue with our employee's example. HR now is the master department updating or inserting employee information. I can then reuse this table to create not only my helpdesk tracking system but say a manager wanted an org chart. He / she wants some sort of organizational hierarchy listing managers and the employees that report to their managers. Well great you think, you have a common database table "Employees" which has fields such as EmployeeName, Status, ManagerID. You can then quickly build an org. chart application using this same common data:
SELECT ManagerID as TheManager, EmployeeName
FROM Employee
WHERE Status='Active'
AND ManagerID="SomeManagerID"
Now you've got a set of employees reporting to a manager and you can build a hierarchy based on this. Well what is the point of this second example you may ask. It means that your development becomes MUCH FASTER. You stop worrying about designing another database and you reuse what is common and available to you. It speeds development!