Database Design

[Rhys666]

I'm working on a database design for a maintenance work scheduling application and just wanted to get opinions on methodology.

I can quite simply break down the requirements into logical entities, tasks (individual actions to be performed), jobs (making up a collection of tasks to be performed based on a frequency) and scheduled jobs (unique instances of jobs based on a set completion date).

However, the entities against which scheduled jobs are performed gets a little convoluted. Jobs are recorded against one of three entities, A plan (a polygon of land), a Physical Site (a group of principal assets), or an Asset (one of a collection of assets making up a principle asset).

Now each job has a number of child entities such as allocated resources, lead time and standard hours but these child entities have different dependencies based on the entity at which it will be performed, (Plan, Site, Asset).

I'm figuring the best methodology should be to create unique tables for each entity, (tasks, plans, sites, jobs, scheduled jobs, responsibility) with nullable fields for each possible parent entity. I.E., the JobResponsibility entity would be made up of a unique Id field, SiteId, PlanId, AssetId, and ResourceId with the SiteId, PlanId and AssetId being nullable as the parent should only be one of these. However, as there are further levels of complexity and parent/child relationship I wanted to get an opinion, (or many opinions ), on what others consider best practice in terms of performance, support and management before I get too far ahead of myself to manage the complexity I'm potentially looking at.

Opinions? Articles? Anything at all appreciated...

Rhys

"Vampireware /n/,a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

"I see dead pixels!

 

[Catadmin]

Jobs are recorded against one of three entities, A plan (a polygon of land), a Physical Site (a group of principal assets), or an Asset (one of a collection of assets making up a principle asset).

The simplest thing to do in a situation like this is to create a table for each individual entity (Jobs, Plans, Sites, and Assets). Child Jobs can be kept within the Jobs table and you can do one of two things: 1) add an extra column to indicate Parent Job or 2) create a second table which has JobID and ParentJobID. The second is more normalized.

Anyway, going along with idea #2, create an additional table which joins Jobs with Sites/Plans/Assets. You could create three tables here, if you really want to stick with the completely normalized structure. It may or maynot get messy. I recommend just creating one with three columns: JobID, EntityID and EntityType. JobID will be the unique identifier of the Job (since job names can probably be repeated), EntityID will be the unique identifier of the Plan/Site/Asset and EntityType will be a P for Plan, an A for Asset and an S for Site.

It's always a good idea to create "third party" type tables for joining one table's info to another table's info. It gets really really messy if you add columns on to the main tables to refer back to another table's ID number. Suddenly you can't find anything because you don't have enough indices or far to many indices...

Does that help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???