1

I am currently designing a module where a service or console application will be reading Job from a JobQueue table. I have designed the JobQueue table with the following info in it:

JobId: int Primarykey

JobType: int notnull

State: varchar notnull

CreatedOn: datetime notnull

LastModifiedOn: datetime nullable

CompletedOn: datetime nullable

The JobType attribute is mainly introduced to keep this table more generic, than towards particular type of job. My question is whether to keep the details for the job to be executed in this table itself or some other table with job id referencing it? This is because i have the data available in some other table. So while executing the job we can indirectly fetch the data. To avoid duplicating the info, i thought of something like this. Is this a good approach? Need your advice on this

Vinoth
  • 2,419
  • 2
  • 19
  • 34

2 Answers2

0

I don't see many attributes here (maybe you did not show those?)

I see an id (jobID) clearly needed, type and state information (can't see how you can do without this data.)

And then you have auditing information, created, modified, and completed. Myself I like to put a string in there for who created and who modified, but I have that auditing information in every table I create. It is always useful when you are not sure what happened and you are trying to maintain the database.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • Thanks Hogan for your reply.. I am maintaining the who created, modified info also in the table. But my main doubt is whether to have data that is needed for the job to execute as the part of jobqueue table itself or some other table cos i have data available in some other table and it can be fetched from there – Vinoth Jul 27 '12 at 12:59
  • What data are you talking about? Something you did not show in your question? – Hogan Jul 27 '12 at 13:01
  • Yeah something thats not part of the jobqueue table. But it is with that data, job will be performed. – Vinoth Jul 27 '12 at 13:43
  • Ok, well if you show the data I can comment, but it seems that you don't *want* to put it in this table, you didn't even include it with the question. I'm lost. Why were you considering moving it to this table? – Hogan Jul 27 '12 at 14:04
  • I never said about moving data to this table. The data is nothing but the details needed for the job to execute, its just an input to the job. Its something the application will insert into some table say X table, during runtime. I dont want that data to be part of this JobQueue table in addition to residing in some other X table. Am i clear now? – Vinoth Jul 27 '12 at 14:11
  • No, what exactly is your question? If your question is "Am I doing the right thing?" Then yes. This is the way relational databases are used. – Hogan Jul 27 '12 at 14:17
0

Keep whatever data you have in that "other table" in that table. What you need to consider here is how you are going to use the data.
If you are going to display some indexed job from where one can select one to view the details, you can get your data in two separate fetches. Or if you need to all the data (data in JobQueue table plus data in that "other table") related to a job, you can always execute a single fetch with JOINs. I don't think keeping JobDetails data on a separate table would cause any issue.

atiyar
  • 7,762
  • 6
  • 34
  • 75