locked
How to model the relations between those 3 tables RRS feed

  • Question

  • Hi,

    I have three tables: one for devices, one for maintenance procedures and finally, one for maintenance programs.  The relations are quite complex and I'm not sure to know how to model them in a way to prevent incoherences.

    All devices have a model number.  The maintenance procedures are linked to one or more model number (I added a new table to model this link).  A maintenance program is a list of some maintenance procedures associated with a device with matching model number.  Procedures and programs have a period and their period must match (like annual, monthly, etc.)

    The same procedure can be linked to more than one device in more than one program, but a procedure-device couple must be unique and associated to only one maintenance program.  I created a table with all the procedure-device-program associations, but I'm not sure it's best solution.  It could lead to inconsistencies in the database.

    Any advices?

    Thank you!

    Tuesday, November 9, 2010 6:14 PM

Answers

  • Hi Faelenor,

    Also Hugo, in the ProgramSpecifications table, the candidate keys you provided are wrong, except for (DeviceNumber, ProcedureCode).  This is the only unique pair in the table.  The same device can be in the same program more than once, as long as the procedures are different (enforced by the previous key).  The same program can also contain the same procedure, but with different devices.

    For example: one car may need two maintenance tasks to be done on the same day (program).  Also, two different cars may need the same maintenance task to be done on the same day.

    Ah, I did not know that. This just shows how important it is to
    provide ample information. (Funny thing is, when I wrote my previous
    post I found myself wondering if it was even allowed to have a device
    in more than one program, as not even that is explicitly stated in
    your posts - unless I overlooked something).

    Yes, given this new information, (DeviceNumber, ProcedureCode) is
    indeed the only candidate key in this table. And that also makes the
    DeviceProcuderes table in my proposed design completely redundant,
    so please disregard the reply I sent five minutes ago to DoolinDalton.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 7:49 PM
  • I'm afraid I'm guilty of causing confusion. With hindsight, I now
    realise that the DeviceProcedures table was redundant right from the
    start.

    I only realised this now, because I missed two things in my initial
    reply:
    - The foreign key constraint between ProgramSpecifications and
    *DeviceProcedures*;
    - The fact that these two tables have the same candidate key.

    A foreign key on a candidate key implies a subtype/supertype
    relationship. But such a relationship is usually only meaningful if
    there are attributes that apply to all occurences (these are model on
    the supertype), and attributes that apply to a subset (model on the
    subtype).

    In this case, you could use DeviceProcedures as a list of valid
    combinations, and ProgramSpecification as a list of combinations
    that have been assigned a program - but assuming that all valid
    combinations will end up in a program (as I'd expect in the car
    analogy), the use of that design becomes limited - why not assign a
    ProgramID as the same time you enter the valid combinations? Then
    you're done in one pass. And you don't end up with two tables where
    one is basically a copy of the other, less two columns.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 8:54 PM
  • Hi DoolinDalton,

    Hugo, Do the following tables meet your description?

    Yes, as far as I can see they do. Thanks!

    I didn't implement what you were saying about unique contraint on ProgramSpecifications because I didn't really understand what you meant by "implement the rest as unique constraints". What is "rest" if I choose DeviceNumber and ProcCode to be my PK columns?

    I made some incorrect assumptions on this table. I thought that a
    model could have only one procedure in a program, and that a procedure
    could only be for a single model in a procedure. So I was thinking about something like this:

      PRIMARY KEY (DeviceNumber, ProcCode),
      UNIQUE (DeviceNumber, ProgramID),
      UNIQUE (ProcCode, ProgramID)

    And in this case, it's not really important which of the three
    candidate keys is promoted to PRIMARY KEY, and which are implemented
    as a UNIQUE constraint.

    But after seeing the new information Faelenor posted, I fully agree
    with having only the (DeviceNumber, ProcCode) key and no others.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 7:56 PM

All replies

  • Trying to understand what a "program" is. Can we use maintainng car as an example.

    So, a car needs maintenance on engine, tires, and brakes. Let's say there are various models of engines, tires, and brakes. So, these would be your model numbers.

    The procedures would be things like, change motor oil, change transmission fluid, rotate tires, change tires, change brakes. These maintenance procedures apply to only certain model numbers.

    So, what would be the "program" using this example?

    Tuesday, November 9, 2010 8:16 PM
  • Excellent example!  To complete it, let say we're a car rental company with a lot of different cars.  A maintenance program would be a list of maintenance procedures to be done on a set of cars.  The maintenance procedures have their period (monthly, annual, etc.) and are grouped in maintenance programs.  A program can only contain procedures with the same period, because a it will be executed in one shot.

    For example, if we have 8 cars of 4 different models (2 of each), we could have this:

    Procedures:
    Change oil procedure for model 1 - Biannual
    Change oil procedure for model 2 - Biannual
    Change oil procedure for model 3 - Biannual
    Change oil procedure for model 4 - Biannual
    Rotate tires (all models) - Biannual

    Program 1 - Biannual
    Change oil model 1, cars 1 and 2
    Change oil model 2, cars 3 and 4
    Rotate tires cars 1, 2, 3 and 4

    Program 2 - Biannual
    Change oil model 3, cars 5 and 6
    Change oil model 4, cars 7 and 8
    Rotate tires cars 5, 6, 7 and 8

    Program 1 and 2 have to be executed 2 times a year, but to divide work more evenly, they probably won't be executed together.

    Here are the rules:

    1. The same procedure can apply to more than one model.
    2. The same procedure can be in more than one program.
    3. A program can only contain procedures with matching period.
    4. A procedure - device pair appears in one and only one program.

    My problem is with points 3 and 4.  I don't know how to enforce these rules.

    I hope it's clearer now and thank you for your help.

    Wednesday, November 10, 2010 1:10 PM
  • OK, I think I see what the thinking behind rules 3 and 4 are. Next, I don't know if this matters, but you have 2 programs that are both biannual, but covering different group of cars.... hmmm. Here are some thoughts before we start drawing up the tables.

    Shouldn't a program be just a grouping of procedures that are to be done together, independent of the vehicles? Also, a program would mapped to vehicle models, not to the individual vehicles. In other words, all you need to describe is how a Honda Civic gets maintained. But since you may have a fleet of 100 Civics, and you can't to maintenance on them all together, you need to spread them out. So that's why you have rule 4.

    So, implicit in what you described, you have a program which I clarified above, and a concept of maintenace calendar which describes when a program is applied to a vehicle.

    Does this make sense and does it apply to your original thinking?

     

    Wednesday, November 10, 2010 2:51 PM
  • Yes, I think it makes sense, but I'll have to confirm with my client.  It still unclear to me how the user will edit all those links, but that's my next problem...

    How would you model the tables with your suggestion?  I don't have much experience in database design...

    Wednesday, November 10, 2010 4:07 PM
  • How is this for a start? How did you get this kind of a gig without much modelling experience? And what are thery paying you? =)

    Anyway, This is still missing the critical pieces which will enforce rules 3 and 4. Let's start with this for now.

    -- Contains all your "device" models.
    -- For cars, it would be things like Honda Civic, Honda Accord, etc.
    create table Models (
    id int identity(1,1) not null,
    model_year int not null,
    manufacturer varchar(50) not null,
    model_name varchar(50) not null
    )
    go
    -- Add PK
    alter table Models
    add constraint PK__Models
    primary key (id)
    go
    -- Enforce unique on model year, manufacturer, and model_name.
    -- This enforces that you can't have multiple entries of 2010 Honda Civic.
    alter table Models
    add constraint UNC__Models__model_year__manufacturer__model_name
    unique nonclustered (model_year, manufacturer, model_name)
    go
    
    -- drop table Models
    
    -- Contains all your fleet of vehicles.
    -- You may have 100 Honda Civics.
    create table Vehicles (
    id int identity(1,1) not null,
    model_id int not null,
    vin varchar(50) not null
    )
    go
    -- Add PK
    alter table Vehicles
    add constraint PK__Vehicles
    primary key (id)
    go
    -- Add FK to Models
    alter table Vehicles
    add constraint FK__Vehicles__Models__model_id
    foreign key (model_id) references Models(id)
    go
    
    --drop table Vehicles
    
    -- Contains all your procedures, like change motor oil, change transmission fluid, rotate tires, etc.
    -- It is generic in this table. There is no relationship to any models at this point.
    create table Procedures (
    id int identity(1,1) not null,
    procedure_desc varchar(100) not null
    )
    -- Add PK
    alter table Procedures
    add constraint PK__Procedures
    primary key (id)
    go
    
    -- drop table Procedures
    
    -- Contains the relationship between Procedures and Models.
    -- This is where interval is defined.
    -- The same procedure may have different intervals depending no the model.
    create table ModelProcedures (
    id int identity(1,1) not null,
    model_id int not null,
    procedure_id int not null,
    interval int not null -- maintenance interval in months.
    )
    -- Add PK
    alter table ModelProcedures
    add constraint PK__ModelProcedures
    primary key (id)
    go
    -- Add FK to Models
    alter table ModelProcedures
    add constraint FK__ModelProcedures__Models__id
    foreign key (model_id) references Models(id)
    go
    -- Add FK to Procedures
    alter table ModelProcedures
    add constraint FK__ModelProcedures__Procedures__id
    foreign key (procedure_id) references Procedures(id)
    go
    -- Enforce unique on model_id and procedure_id.
    -- This enforces you can have only one entry for a model and a procedure.
    -- You don't want to see oil change for 2010 Honda Civic listed more than once.
    alter table ModelProcedures
    add constraint UNC__ModelProcedures__model_id__procedure_id
    unique nonclustered (model_id, procedure_id)
    go
    
    
    
     
    
    
    
    Wednesday, November 10, 2010 5:29 PM
  • How is this for a start? How did you get this kind of a gig without much modelling experience? And what are thery paying you? =)

    Well, I think I exaggerated a bit when I said that I don't have much experience in DB.  I have some, but I mostly learned by myself (oh, and in a database course).  I'm an experienced computer engineer, but specialized in video games.  This DB app is just a side line.

    I wanted to get some help to make sure that I'm in the right direction.  What you proposed is not too far from what I first thought, so it's a good sign.  By the way, this part is an extension to an existing application I began 2 years ago.

    Thanks a lot for your help, I'll analyze that more thoroughly tonight.  I'm still not sure about how I'll make the maintenance programs (or schedules).

    Wednesday, November 10, 2010 6:37 PM
  • The above tables don't enforce rules 3 and 4. But I believe they give you the basic components. Now, thinking about it a bit more, I think you just need one more table and some stored procedures to implement rules 3 and 4. Perhaps you were trying to think of some table constraints for 3 and 4, like I was, but I don't think these rules can be enforced with any kind of column or table constraints based on the model I proposed above, which I think is a good starting point. If you want to enforce rules 3 and 4 with keys and contraints, then I think you can but that would require a model different from the above.

    SO, ASSUMING THE ABOVE IS ROBUST AND REPRESENTATIVE, then I think you just need one more table to complete this. That would be the Maintenance History table. (Thinking about this a bit more, I don't think you need a Program table.) The following table will have a history of all maintenance done to a vehicle.

    create table MaintenanceHistory (
    id int identity(1,1) not null,
    vehicle_id int not null,
    modelprocedure_id int not null,
    yy int not null,
    mm int not null,
    dd int not null
    )
    go
    -- Add PK
    alter table MaintenanceHistory
    add constraint PK__MaintenanceHistory
    primary key nonclustered (id)
    go
    -- Add FK to ModelProcedures
    alter table MaintenanceHistory
    add constraint FK__MaintenanceHistory__ModelProcedures__id
    foreign key (modelprocedure_id) references ModelProcedures(id)
    go
    -- Add FK to Vehicles
    alter table MaintenanceHistory
    add constraint FK__MaintenanceHistory__Vehicles__id
    foreign key (vehicle_id) references Vehicles(id)
    go
    -- Enforce unique on vehicle_id and date.
    -- You dont want to see repeating maintenance dates for the same vehicle
    alter table MaintenanceHistory
    add constraint UC__MaintenanceHistory__vehicle_id__modelprocedure_id__yy__mm__dd
    unique clustered (vehicle_id, modelprocedure_id, yy, mm, dd)
    go

    So far we haven't enforced rules 3 and 4. These will be enforced by some stored procedures.

    Let's go back to ModelProcedures table. That table tells you everything you need to know about a model's (thus a vehicle's) maintenance needs. This information combined with the information in MaintenanceHistory, you should be able to write a stored procedure to gives you a report of the upcoming ModelProcedures. And if we do this right, naturally, you will be performing procedures that are of like intervals (your rule 3 and the concept of Programs) and no same vehicle will get repeated maintenance (your rule 4).

    The details of the stored procedure at this point is trivial.

    Wednesday, November 10, 2010 8:59 PM
  • Hi Faelenor,

    I'm not sure if I understand you completely correctly, but if I do,
    then I think you need to model as follows:

    NOTE - I assumed (you didn't specify) that a model can be subject to
    more than one maintenance program. In the car analogy, an oil change
    and filter cleansing every 5,000 miles, and a total check every 15,000
    miles.

    Table: Procedures
    Two columns:
    - Identifier for procedures (you didn't tell us - I'll assume you have
    a ProcedureCode);
    - Period.
    Primary key on ProcedureCode
    Unique constraint on (ProcedureCode, Period) [this may seem redundant,
    but you'll see why we need it shortly)]

    Table: Devices
    One column:
    - Identifier for devices (you didn't tell us - I'll assume you have a
    DeviceNumber).
    Primary key on DeviceNumber

    Table: Program
    Two columns:
    - Identifier for programs (you didn't tell us - I'll assume you have a
    ProgramID);
    - Period.
    Primary key on ProgramID
    Unique constraint on (ProgramID, Period) [again, not as redundant as
    it seems]

    Table: DeviceProcedures
    Two columns:
    - DeviceNumber
    - ProcedureCode
    Primary key on (DeviceNumber, ProcedureCode)
    Foreign key on DeviceNumber
    Foreign key on ProcedureCode

    Table ProgramSpecifications
    Four columns:
    - ProgramID
    - DeviceNumber
    - ProcedureCode
    - Period
    Three candidate keys: (ProgramID, DeviceNumnber); (ProgramID,
    ProcedureCode); (DeviceNumber; ProcedureCode) - choose one to be the
    primary key, and implement the rest as unique constraints.
    Foreign key on DeviceNumber
    Foreign key on (ProgramID, Period)
    Foreign key on (ProcedureCode, Period)

    The column Period in this table is actually redundant. But including
    it anyway allows us to enforce the requirement that period of program
    and procedure match. This is done by replacing the foreign keys you
    would expect (on ProgramID only and ProcedureCode only) with foreign
    keys on said column plus PeriodID. Since a foreign key must reference
    columns constrained by a primary key or unique constraint, we had to
    add the redundant rwo-column unique constraint in the referenced
    tables.
    The new foreign key constraint implies the old, single-column foreign
    key constraint, so the latter is not needed.

    I think this model accutalely enforces your requirements. Let me know
    if you have any further questions.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, November 10, 2010 11:28 PM
  • Thank you to both of you.  Hugo, I really like your solution.  It's closer to my initial design, you just added some constraints that I didn't think of.  I'll be able to complete my work with all your suggestions.  Thanks again!
    Thursday, November 11, 2010 12:32 PM
  • Hugo, How does DeviceProcedures play a role in your model? I don't see how that table helps ProgramSpecifications table. I can see your model working without the DeviceProcedures table.

    Thursday, November 11, 2010 2:49 PM
  • Yes, you're right Doolin.  I did not pay attention to this table, but it really seems to be useless.

    Also Hugo, in the ProgramSpecifications table, the candidate keys you provided are wrong, except for (DeviceNumber, ProcedureCode).  This is the only unique pair in the table.  The same device can be in the same program more than once, as long as the procedures are different (enforced by the previous key).  The same program can also contain the same procedure, but with different devices.

    For example: one car may need two maintenance tasks to be done on the same day (program).  Also, two different cars may need the same maintenance task to be done on the same day.

    Thursday, November 11, 2010 3:58 PM
  • Hugo, Do the following tables meet your description?

    I left out DeviceProcedures because it doesn't seem to add any value to the model the way it is right now... unless you put a single column key there and use that key as an FK in ProgramSpecifications. Also, I didn't implement what you were saying about unique contraint on ProgramSpecifications because I didn't really understand what you meant by "implement the rest as unique constraints". What is "rest" if I choose DeviceNumber and ProcCode to be my PK columns?

    Anyway, having said all that, here is the create table script. I think I know where you are going with this, but want to test with some real data.... I'm not sure if this model is going to meet some of the rules.

    create table Procedures (
    ProcCode varchar(20) not null,
    Period int not null
    )
    alter table Procedures
    add constraint PK__Procedures
    primary key (ProcCode)
    alter table Procedures
    add constraint UNC__Procedures__ProcCode__Period
    unique nonclustered (ProcCode, Period)
    
    create table Devices (
    DeviceNumber varchar(20) not null
    )
    alter table Devices
    add constraint PK__Devices
    primary key (DeviceNumber)
    
    create table Programs (
    ProgramID varchar(20) not null,
    Period int
    )
    alter table Programs
    add constraint PK__Programs
    primary key (ProgramID)
    alter table Programs
    add constraint UNC__Programs__ProgramID__Period
    unique nonclustered (ProgramID, Period)
    
    create table ProgramSpecifications (
    ProgramID varchar(20) not null,
    DeviceNumber varchar(20) not null,
    ProcCode varchar(20) not null,
    Period int
    )
    alter table ProgramSpecifications
    add constraint PK__ProgramSpecifications
    primary key (DeviceNumber, ProcCode)
    alter table ProgramSpecifications
    add constraint FK__ProgramSpecifications__Devices__DeviceNumber
    foreign key (DeviceNumber) references Devices(DeviceNumber)
    alter table ProgramSpecifications
    add constraint FK__ProgramSpecifications__Programs__ProgramID__Period
    foreign key (ProgramID, Period) references Programs(ProgramID, Period)
    alter table ProgramSpecifications
    add constraint FK__ProgramSpecifications__Procedures__ProcCode__Period
    foreign key (ProcCode, Period) references Procedures(ProcCode, Period)
    Thursday, November 11, 2010 7:13 PM
  • Hi DoolinDalton,

    Hugo, How does DeviceProcedures play a role in your model?

    Good question. I created it because of this sentence in the Faelenors
    original post: "The maintenance procedures are linked to one or more
    model number"
    It should play a larger role in the model than I actually gave it.
    That's because I forgot to include a foreign key constraint on
    (DeviceNumber, ProgramID) in ProgramSpecifications. If you add that
    constraint, you ensure that no one enters a program with a combination
    of model and procedure that is not valid.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 7:44 PM
  • Hi Faelenor,

    Also Hugo, in the ProgramSpecifications table, the candidate keys you provided are wrong, except for (DeviceNumber, ProcedureCode).  This is the only unique pair in the table.  The same device can be in the same program more than once, as long as the procedures are different (enforced by the previous key).  The same program can also contain the same procedure, but with different devices.

    For example: one car may need two maintenance tasks to be done on the same day (program).  Also, two different cars may need the same maintenance task to be done on the same day.

    Ah, I did not know that. This just shows how important it is to
    provide ample information. (Funny thing is, when I wrote my previous
    post I found myself wondering if it was even allowed to have a device
    in more than one program, as not even that is explicitly stated in
    your posts - unless I overlooked something).

    Yes, given this new information, (DeviceNumber, ProcedureCode) is
    indeed the only candidate key in this table. And that also makes the
    DeviceProcuderes table in my proposed design completely redundant,
    so please disregard the reply I sent five minutes ago to DoolinDalton.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 7:49 PM
  • Hi DoolinDalton,

    Hugo, How does DeviceProcedures play a role in your model?

    Good question. I created it because of this sentence in the Faelenors
    original post: "The maintenance procedures are linked to one or more
    model number"
    It should play a larger role in the model than I actually gave it.
    That's because I forgot to include a foreign key constraint on
    (DeviceNumber, ProgramID) in ProgramSpecifications. If you add that
    constraint, you ensure that no one enters a program with a combination
    of model and procedure that is not valid.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis


    Ok, That makes more sense. Also, you wouldn't be able to add a surrogate key to DeviceProcedures and reference that single key from ProgramSpecifications because of the Period rule you need to enforce in ProgramSpecifications, correct?

     

    Thursday, November 11, 2010 7:55 PM
  • Hi DoolinDalton,

    Hugo, Do the following tables meet your description?

    Yes, as far as I can see they do. Thanks!

    I didn't implement what you were saying about unique contraint on ProgramSpecifications because I didn't really understand what you meant by "implement the rest as unique constraints". What is "rest" if I choose DeviceNumber and ProcCode to be my PK columns?

    I made some incorrect assumptions on this table. I thought that a
    model could have only one procedure in a program, and that a procedure
    could only be for a single model in a procedure. So I was thinking about something like this:

      PRIMARY KEY (DeviceNumber, ProcCode),
      UNIQUE (DeviceNumber, ProgramID),
      UNIQUE (ProcCode, ProgramID)

    And in this case, it's not really important which of the three
    candidate keys is promoted to PRIMARY KEY, and which are implemented
    as a UNIQUE constraint.

    But after seeing the new information Faelenor posted, I fully agree
    with having only the (DeviceNumber, ProcCode) key and no others.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 7:56 PM
  • .... And that also makes the DeviceProcuderes table in my proposed design completely redundant,
    so please disregard the reply I sent five minutes ago to DoolinDalton.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

    Hmmm, I just saw this, and dont' really get it right now. I'm closing shop for today will re-engage tmrw. This is a good exercise and I would like to play with some real data. I was in the middle of preparing something. Will resume tmrw.
    Thursday, November 11, 2010 8:03 PM
  • >Also, you wouldn't be able to add a surrogate key to DeviceProcedures and reference that single key from ProgramSpecifications because of the Period rule you need to enforce in ProgramSpecifications, correct?
    Right.
    Plus, I'm not a big fan of surrogate keys anyway.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 8:43 PM
  • I'm afraid I'm guilty of causing confusion. With hindsight, I now
    realise that the DeviceProcedures table was redundant right from the
    start.

    I only realised this now, because I missed two things in my initial
    reply:
    - The foreign key constraint between ProgramSpecifications and
    *DeviceProcedures*;
    - The fact that these two tables have the same candidate key.

    A foreign key on a candidate key implies a subtype/supertype
    relationship. But such a relationship is usually only meaningful if
    there are attributes that apply to all occurences (these are model on
    the supertype), and attributes that apply to a subset (model on the
    subtype).

    In this case, you could use DeviceProcedures as a list of valid
    combinations, and ProgramSpecification as a list of combinations
    that have been assigned a program - but assuming that all valid
    combinations will end up in a program (as I'd expect in the car
    analogy), the use of that design becomes limited - why not assign a
    ProgramID as the same time you enter the valid combinations? Then
    you're done in one pass. And you don't end up with two tables where
    one is basically a copy of the other, less two columns.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 8:54 PM
  • I'm afraid I'm guilty of causing confusion. With hindsight, I now
    realise that the DeviceProcedures table was redundant right from the
    start.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis


    Hugo, So then, would you say the above create table script reflects your design? If so, then I think I see how it meets Faelenor's 4 rules. From here, I would think about adding some kind of maintenance history capability.

     

    Friday, November 12, 2010 3:18 PM
  • Hugo, So then, would you say the above create table script reflects your design?

    I think I already answered that question before. Yes, at this point I
    see no errors.

    From here, I would think about adding some kind of maintenance history capability.

    Why? There is noting in Faelenor's posts that suggest his users need
    that.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Friday, November 12, 2010 11:53 PM
  • Why? There is noting in Faelenor's posts that suggest his users need
    that.

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

    There is no specific mention of that, but without something like that how would you know when the next maintenance is due? Just anticipating where he might be going with this, which is why I suggested the model I did in the beginning.
    Monday, November 15, 2010 2:47 PM
  • It would indeed be a really good feature, but my client doesn't need it, at least for now.

    Monday, November 15, 2010 4:27 PM