Create two separate tables for entities separated by one attribute?

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 op开发者_JAVA百科tion, 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.

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"

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.