Create two separate tables for entities separated by one attribute?

Question

Suppose I have a table called Apple_Jobs.

It defines jobs coming from the Apple_Server.

Now suppose I have another server called Orange_Server

Should I

• modify the existing Apple_Jobs table with an attribute called Origin defining where the job came from - AppleServer or OrangeServer.

OR

• make a separate Orange_Jobs table.

With the first option, I would need to modify certain applications existing querys and stored procedures to only include AppleServer origin jobs, with the second option I would not need to modify any stored procedures.

The structure of each table is the same.

Answer 1

If you find yourself creating tables called AppleServer OrangeServer PearServer

and even PomograniteServer

you have a serious problem, to fix it create a field called type with avalaible values such as "Apple", "Orange", "Pear" and the notorious "Pomogranite".

Do not keep adding tables it will make your life misreable. If you need a specific fruit server you then query the one table:

SELECT blah FROM MyTable WHERE Type="Orange"

Answer 2

Always aim for maximum database normalization (where it makes sense, of course). In your case, I would suggest you create a ServerType (look-up/meta) table, which will contain a list of your server types (e.g. Orange, Apple, Watermelon, etc). Then add a ServerTypeID column to your Jobs table, which will be the foreign key to the ServerType table.