Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    DB Normalization

    This is a very simple question but I've decided to ask it anyways.
    I have a workOrder table that is going to keep track of workOrders. The issue i have is that a workOrder can either be associated to a business or a 'walk-in customer' but not both. It cannot be both at the same time.

    So the table would look like this:

    workOrder(*workOrderID, planNumber, businessID, walkInID, etc)

    or

    workOrder(*workOrderID, custType, custID, etc.) where customer type would either be 'walkin' or 'business' and customerID would be the appropriate foreign key.

    In the previous I would have a null field and that would determine if that workOrder belongs to a business or walkin. The later wouldn't have a null key.

    Obviously i would be storing the business and walkin customers in different tables:

    walkIn(*walkInID, firstName, lastName, etc)
    business(*businessID,companyName, etc)


    Thanks

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I would go with the second option. In fact I might take it further and structure the work order as a super-type sub-type relationship with two subtypes of business and walk-in.

    SuperType
    WorkOrder(WorkOrderID, WorkOrderType)

    SubTypes
    BusinessWO(WorkOrderID, BusinessID, (business work order details))

    WalkInWO(WorkOrderID,WalkInID, (walkin work order details))

    Business/WalkIn Entities
    Business(BusinessID, etc)

    WalkIn(WalkInID, etc)

    Don't have enough information to make a firm conclusion.

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    thx for the reply certus.

    You do have a good idea, but is it really worth it is the question. The thing is, work orders are the EXACTLY same format for businesses and walk in customers (with builders i should have been a little more specific. businesses are builders aka regular customers). The exact same information is used for both records except that the business and walkin entities are recorded in different tables. If i went with your suggestion, then there would be a lot of duplicate data. But if this is the way to do it according to 4NF or 3NF, then i'm all game. Is it though?

    Now, if i do go for the first example, it would be easy to pick apart which work order is for a walkin customer or which order is for a business by determining which field (either businessID or contactID) is null.

    So yeah, I just wanted to know the "right" way of doing this from you who have more experience than i. Any thoughts?

    If you need more info, please let me know.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You can't have a foreign key that references 2 different tables, so your first idea is the right one. You may also want a check constraint to enforce that one or the other (and not both) is populated.

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You can do it like this:

    SuperType:
    WorkOrder(WorkOrderID, (work order details), WorkOrderType)

    SubTypes:
    BusinessWO(WorkOrderID, BusinessID)
    WalkInWO(WorkOrderID,WalkInID)

    Business/WalkIn Entities:
    Business(BusinessID, etc)
    WalkIn(WalkInID, etc)

    The subtypes will allow you to discriminate between whether the work order is a business or walk in work order. The structure is sound and is sometimes called a "generalization hierarchy".

    http://www.utexas.edu/its/windows/da...erarchies.html

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Here is a point to consider, If you will be required to display information about a work order, specifically about the entity to which the order relates to then by using child-entities the following steps would be required.

    1) Determine which child table the workOrder ID exists in.
    2) Locate the matching tuple, from one of these child tables.
    3) Using either the businessID or walkinID, search the appropiate details table to retrieve information about either of these entities.

    Using the first option with null values the above can be completed by,

    1) Locate the tuple in the parent table.
    2) Use the not null value to return the extended details.

    This is just to identify the different steps required with each option.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    What's happening is we have logical design and physical design talking past one another.

    Get your logical design worked out and then think about the constraints of physical design. Otherwise you're building without a blueprint.

  8. #8
    Join Date
    Oct 2003
    Posts
    706
    As a general rule, I would say that a "work order" is created by "a customer" and, on the surface of it, this ought to be one table whether walk-in or not.

    However, reality check time!

    The word "always" exists very often in the land of computerdom, but it doesn't exist nearly so much in the real world, where the most common word is, "except!"

    What are you going to do, for example, if the person who is entering the order does not have the customer details on-hand, and wants to enter them later? What if they need a "special, internal kind of work order" (I'm making this up, just like end-users in senior management do) "just this once, in order to track the job of fixing the company car?" This request is not "wrong," it's not "unresonable" ... it's justifiable. Too bad somebody didn't know about it or think about it when designing the system. A company cannot wait for its I.S. department.

    What I'm driving at (ahem) is that it is very possible to build a very pristine, very "correct" database schema which as a result is not flexible enough to represent the real world. This will cause users to "fudge" things, perhaps creating a dummy customer-ID, just to allow the system to be as flexible as, say, a pencil and a pad of work-order forms used to be.

    Or maybe they won't actually use the system for everything, and will keep stuff on sheets of paper stuck between the computer and the monitor. Important stuff! People can be amazingly creative and resourceful, just to get the work done when the I.S. department has delivered a system that can't handle everything that actually goes on.

    "Oh yeah, oh yeah, you betcha! We told 'em about this, five years ago, when they started building this piece of ... and now they tell us it'll be at least three years before they can 'get around to it' so we just told 'em they could just go and ( ...) and meanwhile, this is how we actually do things around here. It may not be elegant but it works and that's what matters to me."
    --Senior Vice-President of Operations and close personal friend of every member of the Board of Directors...
    Lotsa systems out there like that. Don't let the next one be yours.
    Last edited by sundialsvcs; 12-12-03 at 15:28.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    some real fine gems of wisdom in there, mister sundial
    I'm making this up, just like end-users in senior management do
    This request is not "wrong," it's not "unreasonable" ... it's justifiable.
    A company cannot wait for its I.S. department.
    rudy

  10. #10
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    All it takes to rectify that perspective is to acknowledge that all systems should be and ultimately are incremental.

  11. #11
    Join Date
    Oct 2003
    Posts
    73
    Thx for all the replies!
    Some very good points mentioned there. Im gonna look closely at certus's design cause that is similiar to what I was looking for and its normalized. Although making it that normalized does have some drawbacks in terms of how many operations it takes to determine the owner of a work order, I'm gonna look into that further yet before i make my decision.
    Also, some very goos points there by sundial and how i can relate!

    thx again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •