Professional Web Applications Themes

Design: one table or two tables, depending on usage - Microsoft SQL / MS SQL Server

Gidday, I'd create a "mapping" table... one that has a unique identifier that can be linked to the billto field... and also a dept field and an employee field - these would hold whatever you need to link back to dept or employee. You can then create a reference from the map tables key to the billto field, and from the employee table key to the employee field (mapping table), and the dept table key to the dept field (mapping table)... You'll need to allow nulls for the employee and dept field... and it would be smart to use a ...

  1. #1

    Default Re: Design: one table or two tables, depending on usage

    Gidday,

    I'd create a "mapping" table... one that has a unique identifier that can be
    linked to the billto field... and also a dept field and an employee field -
    these would hold whatever you need to link back to dept or employee. You can
    then create a reference from the map tables key to the billto field, and
    from the employee table key to the employee field (mapping table), and the
    dept table key to the dept field (mapping table)...

    You'll need to allow nulls for the employee and dept field... and it would
    be smart to use a trigger or something to ensure any data put into the map
    table had either dept or employee data provided....

    Hope that makes sense... would be easier to draw it with relationships...

    Cheers


    "Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
    news:3f062f1d$1duster.adelaide.on.net...
    > Here's one that must have come up before but I am having trouble coming up
    > with keywords to search google with...
    > First, a simplified model that shows the basic problem. I have left out
    keys
    > for readability, I think the appropriate keys are fairly obvious.
    >
    > create table Accounts(
    > LedgerCode char(4),
    > AccountName varchar(32)
    > )
    >
    > create table Departments(
    > DepartmentCode char(3),
    > LedgerCode char(4),
    > DepartmentName varchar(32)
    > )
    >
    > create table Employees(
    > EmployeeNumber int,
    > DepartmentCode char(3),
    > EmployeeName varchar(32)
    > )
    >
    > create table WorkDone(
    > DateStarted datetime,
    > HoursWorked smallint,
    > ByEmployee int,
    > BillTo ... ?
    > )
    >
    > In english, the above is designed to allow departments within a company to
    > charge other departments based on an hourly rate.
    > The trouble is the WorkDone.BillTo column.
    > Work may be billed against either a department or an employee depending on
    > the type of work submitted. Either way, it will eventually get resolved to
    > an account, but we would like to track the actual source (employee,
    > department), not just the account, in the WorkDone table. Essentially, the
    > problem is how to create a "Who asked for this work?" view / procedure.
    > But then how can the BillTo column refer to both Departments and
    Employees?
    > One solution might be to use GUIDs as employee and department identifiers,
    > and a GUID for the BillTo column in workDone. I'll leave out posting the
    new
    > structure for brevity, I think the change is fairly clear. Excuse any
    errors
    > as I haven't actually built the required structure:
    >
    > create view vwBillableWork
    > as
    > select acc.accountName,
    > tmp.type,
    > tmp.name,
    > wrk.hoursWorked
    > from Accounts acc
    > join (
    > select emp.employeeGUID as GUID,
    > 'Employee' as type,
    > emp.employeeName as name,
    > dep.ledgerCode as ledgerCode
    > from Employees emp
    > join Departments dep on emp.departmentCode =
    > dep.departmentCode
    > union
    > select departmentGUID,
    > 'Department',
    > departmentName,
    > ledgerCode
    > from Departments
    > )
    > tmp on tmp.ledgerCode = acc.ledgerCode
    > join WorkDone wrk on wrk.billTo = tmp.GUID
    >
    >
    > A similar solution is to ignore the GUID, store the type of entity billed
    in
    > the WorkDone, and use that when joining onto the union. I'll refrain from
    > posting the query.
    >
    > But both of these solutions leave WorkDone.BillTo without any referential
    > integrity (unless we write it into database triggers... yuk).
    >
    > So if departments and employees are both billable, do they actually belong
    > in the same table?
    >
    > create table BillableEntity(
    > EntityId int identity(1,1),
    > entityType varchar(20) not null check (entityType in('Employee',
    > 'Department')),
    > entityName varchar(32)
    > )
    >
    > WorkDone.billTo now references BillableEntity.entityId. We could still
    keep
    > the departments and employees table, and use triggers to push records into
    > BillableEntity whenever they are inserted into either of the others. But
    > this seems like a lot of redundancy.
    > Alternatively, we could eliminate the departments and employees tables,
    > eliminating the redundancy, but losing the relationship that they
    describe.
    > I suppose yet another way would be to somehow build that relationship
    > directly into the BillableEntity table as a tree...
    >
    > create table BillableEntity(
    > EntityId int identity(1,1),
    > ParentEntityId int null,
    > EntityName varchar(32)
    > )
    >
    > Type is no longer needed as we know the leaves are the employees and
    > everything else is a department. It also allows nested departments in the
    > future. But it represents a level of complexity that is not required. I
    > would rather avoid hacking the employee / department relationship into the
    > single "BillableEntity" tree.
    >
    > The overriding problem is that employees and departments are either a
    single
    > entity (table), or two entity types (tables) depending on your point of
    > view.
    >
    > Thoughts?
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >

    Bob Guest

  2. #2

    Default Re: Design: one table or two tables, depending on usage


    "Bob" <bobnospam.com> wrote in message
    news:uo2s$ppQDHA.2096TK2MSFTNGP12.phx.gbl...
    > Gidday,
    >
    > I'd create a "mapping" table... one that has a unique identifier that can
    be
    > linked to the billto field... and also a dept field and an employee
    field -
    > these would hold whatever you need to link back to dept or employee. You
    can
    > then create a reference from the map tables key to the billto field, and
    > from the employee table key to the employee field (mapping table), and the
    > dept table key to the dept field (mapping table)...
    >
    > You'll need to allow nulls for the employee and dept field... and it would
    > be smart to use a trigger or something to ensure any data put into the map
    > table had either dept or employee data provided....
    >
    > Hope that makes sense... would be easier to draw it with relationships...
    >
    > Cheers
    Something like this?

    create table Depts(
    deptId int,
    deptName varchar(32)
    )

    create table Emps(
    EmpId int,
    empName varchar(32)
    )

    create table WorkDone(
    workId int,
    hours int
    )

    create table WorkMap(
    workId int,
    empId int null,
    deptId int null
    )
    GO

    create view vwBilling
    as
    select deptName, hours
    from Depts d
    join WorkMap m on m.deptId = d.deptID
    join WorkDone w on m.workId = w.workId
    union
    select empName, hours
    from Emps e
    join WorkMap m on m.empId = e.empId
    join workDone w on m.workId = w.workId

    This seems the same as my first solution, except that the mapping has to
    allow nulls.
    If we put the "type" of entity billed into the work done we can eliminate
    the nulls and the mapping, which I didn't actually post details for but
    essentially...

    create view vwBilling
    as
    select deptName, hours
    from Depts d
    join WorkDone w on w.keyId = d.deptId
    and w.type = 'Department'
    union
    select empName, hours
    from Emps e
    join WorkDone w on w.keyId = e.empId
    and w.type = 'Employee'

    Your way has referential integrity, the "type" way eliminates nulls.
    I think I prefer your mapping solution in that referential integrity seems
    more useful than normalisation. But those nulls still annoy me.



    Donald Halloran Guest

  3. #3

    Default Re: Design: one table or two tables, depending on usage


    "Bob" <bobnospam.com> wrote in message
    news:OohfC4qQDHA.2832TK2MSFTNGP10.phx.gbl...
    > Yeah, that looks right...
    >
    > I was going for the referential integrity...
    >
    > You could always create a dummy record for dept and employee and use that
    as
    > a default... depends on why you are avoiding nulls...
    Partly because I have heard (in passing) that nullable columns don't index
    as efficiently.
    But mainly because I want to have the "right" solution to the problem in
    terms of a relational database. OK so there may not be a "correct" solution
    to a lot of design questions, but I want to make sure of that before I get
    around to implementing one over another.
    "What would Codd do?" :)

    Thanks for the feedback btw.


    Donald Halloran Guest

  4. #4

    Default Re: Design: one table or two tables, depending on usage

    No problem... I avoid nulls also - I think it is better to have a default
    value... or even an empty string depending on datatype...

    Cheers

    "Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
    news:3f0657dc$1duster.adelaide.on.net...
    >
    > "Bob" <bobnospam.com> wrote in message
    > news:OohfC4qQDHA.2832TK2MSFTNGP10.phx.gbl...
    > > Yeah, that looks right...
    > >
    > > I was going for the referential integrity...
    > >
    > > You could always create a dummy record for dept and employee and use
    that
    > as
    > > a default... depends on why you are avoiding nulls...
    >
    > Partly because I have heard (in passing) that nullable columns don't index
    > as efficiently.
    > But mainly because I want to have the "right" solution to the problem in
    > terms of a relational database. OK so there may not be a "correct"
    solution
    > to a lot of design questions, but I want to make sure of that before I get
    > around to implementing one over another.
    > "What would Codd do?" :)
    >
    > Thanks for the feedback btw.
    >
    >

    Bob Guest

  5. #5

    Default Re: Design: one table or two tables, depending on usage

    >> I have left out keys for readability, I think the appropriate keys
    are fairly obvious. <<

    Leaving off keys, NOT NULL constraints, etc. only makes things harder
    not easier. Here is my guess at the DRI and keys -- I have about 5
    other ways that are just as "obvious"; come on, you know that there are
    no "obvious specs", only vague ones.

    CREATE TABLE Accounts
    (ledger_code CHAR(4) NOT NULL PRIMARY KEY,
    account_name VARCHAR(32) NOT NULL);

    --MAJOR PROBLEM!! Can a department have more than one ledger code? The
    answer is usually "Yes", but you never told us.

    CREATE TABLE Departments
    (department_code CHAR(3) NOT NULL PRIMARY KEY,
    department_name VARCHAR(32) NOT NULL,
    ledger_code CHAR(4) NOT NULL
    REFERENCES Accounts(ledger_code));

    -- OR is it this?

    CREATE TABLE Departments
    (department_code CHAR(3) NOT NULL,
    department_name VARCHAR(32) NOT NULL,
    ledger_code CHAR(4) NOT NULL
    REFERENCES Accounts(ledger_code),
    PRIMARY KEY (department_code, ledger_code));

    -- Can I assume an employee has one department?
    CREATE TABLE Employees
    (employee_number INTEGER NOT NULL PRIMARY KEY,
    employee_name VARCHAR(32) NOT NULL,
    department_code CHAR(3) NOT NULL
    REFERENCES Departments (department_code));
    >> designed to allow departments within a company to charge other
    departments based on an hourly rate. The trouble is the WorkDone.BillTo
    column. Work may be billed against either a department or an employee
    depending on the type of work submitted. Either way, it will eventually
    get resolved to an account, but we would like to track the actual source
    (employee, department), <<

    You said the source, but not the individual employee (if any):

    CREATE TABLE WorkDone
    (work_order INTEGER NOT NULL PRIMARY KEY,
    date_started DATETIME NOT NULL,
    date_finished DATETIME NOT NULL,
    CHECK (date_started < date_finished)
    bill_to_ledger_code CHAR(4) NOT NULL
    REFERENCES Accounts(ledger_code),
    CHECK (<< ledger code is a department >>),
    work_type CHAR(1) DEFAULT 'D' NOT NULL -- assume dept as default
    CHECK (work_type IN ('E', 'D')));
    I don't know how your account codes are set up, but I assume that there
    is enough intelligence in the codes to identify a department from the
    syntax of the CHAR(3) strings.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

  6. #6

    Default Re: Design: one table or two tables, depending on usage

    One of the "rites of passage" here is to have me jump all over you :)
    >> I'd create a "mapping" table... one that has a unique identifier that
    can be linked to the billto field [sic]... and also a dept field [sic]
    and an employee field [sic] - these would hold whatever you need to link
    back to dept or employee. You can then create a reference from the map
    tables key to the billto field [sic], and from the employee table key to
    the employee field [sic] (mapping table), and the dept table key to the
    dept field [sic] (mapping table)... <<

    There are no "fields" in SQL -- columns are TOTALLY different. There
    are no "links" in SQL (well, that you can see as user); pointer chains
    belong to pre-relational network databases and that is what you are
    proposing.

    The "map table" is another concept from a network database -- I cannot
    remember the CODASYL name for it, but it allowed you to trace one of
    many paths to build one of many different types of records in the same
    program. SQL is based on tables; tables are sets of the same kind of
    things.

    Read the specs -- work done must be billed against an *account*. We
    don't care which employee or the department did the work; we only want
    to charge the right account. This account must belong to a department.
    How the work was done (department or employee) is an attribute of the
    billing.
    >> You'll need to allow nulls for the employee and dept field [sic]...
    and it would be smart to use a trigger or something to ensure any data
    put into the map table had either dept or employee data provided... <<

    NULLs and especially procedural code in triggers are things to avoid.

    If the billing can be made against an employee apart from his
    department, we know he must have an account from the specs given.
    Therefore, we need a table like this:

    CREATE TABLE WorkDone
    (work_order INTEGER NOT NULL PRIMARY KEY,
    date_started DATETIME NOT NULL,
    date_finished DATETIME NOT NULL,
    CHECK (date_started < date_finished)
    bill_to_ledger_code CHAR(4) NOT NULL
    REFERENCES Accounts(ledger_code),
    work_type CHAR(1) DEFAULT 'D' NOT NULL
    CHECK (work_type IN ('E', 'D')),
    CHECK (CASE WHEN worktype = 'D'
    AND <<acct code is dept>>
    THEN 1
    WHEN worktype = 'E'
    AND <<acct code is emp>>
    THEN 1
    ELSE 0 END = 1));

    --CELKO--

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

  7. #7

    Default Re: Design: one table or two tables, depending on usage


    "Joe Celko" <anonymousdevdex.com> wrote in message
    news:uE%23xZN0QDHA.1072TK2MSFTNGP10.phx.gbl...
    > Leaving off keys, NOT NULL constraints, etc. only makes things harder
    > not easier. Here is my guess at the DRI and keys -- I have about 5
    > other ways that are just as "obvious"; come on, you know that there are
    > no "obvious specs", only vague ones.
    Had a bad day Joe? :)
    > CREATE TABLE Accounts
    > (ledger_code CHAR(4) NOT NULL PRIMARY KEY,
    > account_name VARCHAR(32) NOT NULL);
    >
    > --MAJOR PROBLEM!! Can a department have more than one ledger code? The
    > answer is usually "Yes", but you never told us.
    As I said, I posted a simplified model. When I said the keys were "obvious",
    I was saying "you don't have to think about them, they're clearly named in
    the tables". The "obvious" PK for department is department code. Likewise
    for other tables. You didn't honestly find that difficult, surely.
    > -- Can I assume an employee has one department?
    > CREATE TABLE Employees
    > (employee_number INTEGER NOT NULL PRIMARY KEY,
    > employee_name VARCHAR(32) NOT NULL,
    > department_code CHAR(3) NOT NULL
    > REFERENCES Departments (department_code));
    See, that wasn't so hard :)
    > You said the source, but not the individual employee (if any):
    No, I said the source (employee, department). Do a little substitution here,
    and it becoes two sentences.
    "We would like to track the actual employee".
    "We would like to track the actual department".

    I also said...
    "work may be billed against either a department or an employee".

    And I even said....
    " Essentially, the problem is how to create a "Who asked for this work?"
    view / procedure. But then how can the BillTo column refer to both
    Departments and Employees?"

    And then I sketched out a view that returned hours against individual
    employees and departments.

    Finally, another 3 posts on the topic expanded on this notion.

    You're not Joe Celko, you're an imposter! Tell the real Joe that I'd like to
    hear his thoughts on this (If you see him).


    Donald Halloran Guest

  8. #8

    Default Re: Design: one table or two tables, depending on usage

    Fields, columns, whatever... the term used depends on the technology being
    utilised... SQL uses the term columns, ADO uses the term field.... who
    cares? Petty semantics....

    You have an interesting way of putting your view across... but I think
    reading the thread (or the "specs" as you put it) first would help as you
    might say something useful.... based on the little info provided I gave a
    possible solution... I never said it was the only solution, or even the best
    solution... but it is a well known, often utilised and proven solution...

    If you have a better answer then that's great, present your case...



    "Joe Celko" <anonymousdevdex.com> wrote in message
    news:ODZqlY0QDHA.3664tk2msftngp13.phx.gbl...
    > One of the "rites of passage" here is to have me jump all over you :)
    >
    > >> I'd create a "mapping" table... one that has a unique identifier that
    > can be linked to the billto field [sic]... and also a dept field [sic]
    > and an employee field [sic] - these would hold whatever you need to link
    > back to dept or employee. You can then create a reference from the map
    > tables key to the billto field [sic], and from the employee table key to
    > the employee field [sic] (mapping table), and the dept table key to the
    > dept field [sic] (mapping table)... <<
    >
    > There are no "fields" in SQL -- columns are TOTALLY different. There
    > are no "links" in SQL (well, that you can see as user); pointer chains
    > belong to pre-relational network databases and that is what you are
    > proposing.
    >
    > The "map table" is another concept from a network database -- I cannot
    > remember the CODASYL name for it, but it allowed you to trace one of
    > many paths to build one of many different types of records in the same
    > program. SQL is based on tables; tables are sets of the same kind of
    > things.
    >
    > Read the specs -- work done must be billed against an *account*. We
    > don't care which employee or the department did the work; we only want
    > to charge the right account. This account must belong to a department.
    > How the work was done (department or employee) is an attribute of the
    > billing.
    >
    > >> You'll need to allow nulls for the employee and dept field [sic]...
    > and it would be smart to use a trigger or something to ensure any data
    > put into the map table had either dept or employee data provided... <<
    >
    > NULLs and especially procedural code in triggers are things to avoid.
    >
    > If the billing can be made against an employee apart from his
    > department, we know he must have an account from the specs given.
    > Therefore, we need a table like this:
    >
    > CREATE TABLE WorkDone
    > (work_order INTEGER NOT NULL PRIMARY KEY,
    > date_started DATETIME NOT NULL,
    > date_finished DATETIME NOT NULL,
    > CHECK (date_started < date_finished)
    > bill_to_ledger_code CHAR(4) NOT NULL
    > REFERENCES Accounts(ledger_code),
    > work_type CHAR(1) DEFAULT 'D' NOT NULL
    > CHECK (work_type IN ('E', 'D')),
    > CHECK (CASE WHEN worktype = 'D'
    > AND <<acct code is dept>>
    > THEN 1
    > WHEN worktype = 'E'
    > AND <<acct code is emp>>
    > THEN 1
    > ELSE 0 END = 1));
    >
    > --CELKO--
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Bob Guest

  9. #9

    Default Re: Design: one table or two tables, depending on usage

    >> Had a bad day Joe? :) <<

    Nah! Just 35 years of "obvious specs" behind me :) Did you you know
    that accounting systems in many countries use the cash method rather
    than the accural method? And they think this is so obvious they don't
    mention it until six months into a project ...
    >> The "obvious" PK for department is department code. Likewise for
    other tables. You didn't honestly find that difficult, surely. <<

    Yes, I did; when you put account codes into that table, then I have to
    wonder if there is **one and only one** account code per department
    (seems weird) or if a department has many account codes (payroll,
    expenses, incomes, etc. -- *much* more likely).

    This simple table seems to have bad name -- I would have expected the
    Accounts table to have departmental info in it instead. Using a Uniform
    Chart of Accounts model, a postfix numeric code a la Dewey Decimal to
    give the department.
    >> "We would like to track the actual employee".
    "We would like to track the actual department". <<

    What is the rule for the Bill_to table when an employee is fired and his
    employee number does not exist any more?

    The actual department should be in the account code of the bill-to, if
    you designed the accounting system correctly. The actual employee is
    harder, but should be on a work-ticket or job order associated with his
    time sheets. I see the data model as being done at two levels of
    aggregation (employee within department and department), so they are two
    didfferent types of things and cannot be in the same column in the same
    table. And you bill to an account, not directly to either an employee
    or a department.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

  10. #10

    Default Re: Design: one table or two tables, depending on usage


    "Joe Celko" <anonymousdevdex.com> wrote in message
    news:%23biClsARDHA.2228tk2msftngp13.phx.gbl...
    > This simple table seems to have bad name -- I would have expected the
    > Accounts table to have departmental info in it instead. Using a Uniform
    > Chart of Accounts model, a postfix numeric code a la Dewey Decimal to
    > give the department.
    Ah, I see. No. In the simplified model presented, departments belong to
    accounts, not the other way around. Perhaps "groups" would be a better term
    than "departments".
    > What is the rule for the Bill_to table when an employee is fired and his
    > employee number does not exist any more?
    If the employee is fired then you can't book work against them any more.
    That's not a problem. We don't delete the record. (I think I have missed the
    point of the question, it seems a little odd).
    > The actual department should be in the account code of the bill-to, if
    > you designed the accounting system correctly.
    The accounting system is already in place and has nothing to do with me. We
    are just "hooking into it".
    The reality of the situation is as follows:
    - Accounts already exist
    - Departments (groups) already exist
    The relationship between them is

    [Accounts] 1--many [groups] 1--many [employees]
    > harder, but should be on a work-ticket or job order associated with his
    > time sheets. I see the data model as being done at two levels of
    > aggregation (employee within department and department), so they are two
    > didfferent types of things and cannot be in the same column in the same
    > table. And you bill to an account, not directly to either an employee
    > or a department.
    OK... looks like two tables. But we book work against employees and
    departments. We want to track who, exactly, asked for what. Not just who is
    paying for it. One relationship is for the purpose of figuring out
    "invoices" accounts. The other is to track who is asking for what. If we
    just track the account we lose the latter.

    I guess there is no "correct" relational database solution here.




    Donald Halloran Guest

  11. #11

    Default Re: Design: one table or two tables, depending on usage

    OK, here's what I think is the "proper" solution.
    By the way, I didn't initially mention we also have "tasks" we can book
    against (which belong to a group just like an employee does). It's the same
    problem so it wasn't worth mentioning at the time but I include the complete
    solution here

    (m for many and o for one, fixed width font required)

    [Accounts]
    o
    |
    m
    [Groups]
    o o o
    | | |
    m | m
    [Tasks] | [Employees]
    o | o
    | | |
    m m m
    [TW] [GW] [EW]
    m m m
    | | |
    \ | /
    o o o
    [Work]

    Which is the "simplest" normalised model I suppose. Creating / retrieving
    the information stored this way requires some extra work, but probably not
    so much as to cause major concern. It also doesn't make clear / enforce that
    work must be allocated to one employee OR task OR group, but the only way I
    can see of doing that "cleanly" is to denormalise the TW, GW and EW mapping
    tables into one and put a constraint that says two of the three columns in a
    row must be null, and I don't like that. I suppose an indexed view could do
    the same constraint check (TW union GW union EW, PK workId).


    Donald Halloran Guest

  12. #12

    Default Re: Design: one table or two tables, depending on usage


    "Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
    news:3f08cdfd$1duster.adelaide.on.net...
    >
    > [Accounts]
    > o
    > |
    > m
    > [Groups]
    > o o o
    > | | |
    > m | m
    > [Tasks] | [Employees]
    > o | o
    > | | |
    > m m m
    > [TW] [GW] [EW]
    > m m m
    > | | |
    > \ | /
    > o o o
    > [Work]
    >
    Slight correction: I should say primary key / foreign key sides of the
    relationships rather than one / many because the [?W] to [Work]
    relationships are not really one to many.


    Donald Halloran Guest

  13. #13

    Default Re: Design: one table or two tables, depending on usage


    "Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
    news:3f08cf2f$1duster.adelaide.on.net...
    >
    > >
    > > [Accounts]
    > > o
    > > |
    > > m
    > > [Groups]
    > > o o o
    > > | | |
    > > m | m
    > > [Tasks] | [Employees]
    > > o | o
    > > | | |
    > > m m m
    > > [TW] [GW] [EW]
    > > m m m
    > > | | |
    > > \ | /
    > > o o o
    > > [Work]
    > >
    I don't mean to beat a dead horse, but I don't consider this one dead as I'm
    still not really happy with my solution. I'd like to think someone else
    might find this on google down the line and get something out of it so I'll
    post my thoughts on the continuing problems...

    The problem with the solution above is, as I said, that using this data is
    less than convenient.
    Consider an insert statement. I can take a source (task, group, or
    employee), insert one row into [Work], get the scope_identity, and create
    the relationship back to the source through the appropriate table. But I
    cannot conveniently take a whole set of work rows and do the same thing.
    Similarly, an update statement is at times difficult. If I am updating
    certain attributes of the work (like the number of hours done), which do not
    bear on the source, no problem. But if I want switch the source I have to
    first find out which kind of source it is currently attached to, delete the
    record from the old appropriate map, and then insert into the new map.
    Something like this would be safe, but is rather inelegant (given workId is
    the work record we are updating...)

    delete from taskWork where workId = workid
    if(rowcount = 0)
    begin
    delete from groupWork where workId = workId
    if(rowcount = 0)
    begin
    delete from employeeWork where workId = workId
    if(rowcount = 0) -- we should never get here!
    end
    end

    .... perform the appropriate insert

    Alternatively I could store the sourceType (employee, group or task) in the
    work table as per the original solution...

    select t = sourceType from Work where workId = workId

    if(sourceType = 'group')
    begin
    delete from groupwork where workId = workId
    end
    else if(sourceType = 'employee')
    ... etc

    Which still just doesn't feel right.
    What I really need is to be able to have a view which is updatable despite
    referencing many tables, and which I can foreign key into from [Work].

    create view vwAllSources
    as
    select sourceType, groupId as source from groups
    union
    select sourceType, taskId from tasks
    union
    select sourceType, employeeId from employees

    -- create a primary key on vwAllSources(sourceType, groupId)

    create table ImpossibleWork(
    WorkId ...,
    sourceType ...,
    source ...,
    constraint fk_work_sources foreign key (sourceType, source) references
    vwAllSources(sourceType, source)
    )


    :)





    Donald Halloran Guest

  14. #14

    Default Re: Design: one table or two tables, depending on usage

    I tried to post this via DevDex and it never showed up, so I am going
    in thru Google. The idea is to use overlapping UNIQUENESS constraints
    to enforce the rules. What about team instead of department for these
    work groups? GROUPs has a special meaning in SQL, so I want to avoid
    it.

    -- simple base table
    CREATE TABLE Accounts
    (ledger_code CHAR(4) NOT NULL PRIMARY KEY,
    account_name VARCHAR(32) NOT NULL,
    ..);

    -- simple base table
    CREATE TABLE Employees
    (emp_nbr INTEGER NOT NULL PRIMARY KEY,
    employee_name VARCHAR(32) NOT NULL,
    ..);

    -- life now gets harder team has one ledger code; ledger code has many
    teams
    CREATE TABLE Teams
    (ledger_code CHAR(4) NOT NULL
    REFERENCES Accounts(ledger_code),
    team_code CHAR(3) NOT NULL UNIQUE, -- trick!
    team_name VARCHAR(32) NOT NULL,
    PRIMARY KEY (ledger_code, team_code));

    -- now we put people on teams, one person has one team, a team has
    many people
    CREATE TABLE TeamsAsignments
    (team_code CHAR(3) NOT NULL
    REFERENCES Teams(team_code),
    ledger_code CHAR(4) NOT NULL
    REFERENCES Accounts(ledger_code),
    emp_nbr INTEGER
    REFERENCES Employees(emp_nbr),
    FOREIGN KEY (ledger_code, team_code)
    REFERENCES Teams (ledger_code, team_code),
    UNIQUE (ledger_code, team_code, emp_nbr)); -- UNIQUE allows NULLs;
    important!

    -- use a NULL emp_nbr for team level billing
    CREATE TABLE WorkDone
    (work_order INTEGER NOT NULL,
    task_nbr INTEGER NOT NULL, -- assumption about work reporting
    date_started DATETIME NOT NULL,
    date_finished DATETIME NOT NULL,
    CHECK (date_started < date_finished),
    ledger_code CHAR(4) NOT NULL,
    team_code CHAR(3) NOT NULL,
    emp_nbr INTEGER, -- null means whole team
    FOREIGN KEY (ledger_code, team_code, emp_nbr)
    REFERENCES REFERENCES TeamAssignments (ledger_code, team_code,
    emp_nbr),
    PRIMARY KEY (work_order, task_nbr));

    I think that gets everything, but check me.
    --CELKO-- Guest

  15. #15

    Default Re: Design: one table or two tables, depending on usage


    "--CELKO--" <71062.1056compuserve.com> wrote in message
    news:c0d87ec0.0307081519.469ca180posting.google.c om...
    > work groups? GROUPs has a special meaning in SQL, so I want to avoid
    > it.
    Good point.

    <snip>
    > I think that gets everything, but check me.
    Yes, that is a workable solution, but aren't we violating a normal form
    (BCNF?) by storing the ledger code on both employee and team?
    I was also going to say something similar about having both employee and
    team on the WorkDone... that's where the conceptual foreign key into the
    union of teams and employees would come in. That is still the real
    problem... teams and employees look like the same thing from the work done
    table (a source of work), but different from the accounts side (accounts
    only really belong to teams, not employees). In any case, the solution you
    have here (storing both emp and team on the workDone) is very close to the
    script I first started to write, which made me think about this whole
    problem :) I think for practical purposes it is probably suitable. I didn't
    have the ledger code on the employee table, but I see no *really* good way
    around having both team and (nullable) employee on the workdone table.
    If we pretend for a moment that we can add anything we want to sql and
    relational databases, would there be a better way (like the foreign key into
    the union)? Even that seems clunky.
    In any case, I appreciate your contributions as always.


    Donald Halloran Guest

  16. #16

    Default Re: Design: one table or two tables, depending on usage


    "Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
    news:3f0be1f6duster.adelaide.on.net...
    > table (a source of work), but different from the accounts side (accounts
    > only really belong to teams, not employees).
    Sorry, this is badly phrased. Rather, "the account ledger code (foreign key)
    only really belongs in the teams table". Just thought I'd clean that up.


    Donald Halloran Guest

Similar Threads

  1. Tables don't show in Design View
    By pwalters in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: January 23rd, 05:00 PM
  2. Creating your first site design w/o tables
    By Talguy in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: September 15th, 11:22 PM
  3. DB design...multiple tables for ancillary data or just one?
    By darrel in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 21st, 05:24 PM
  4. Design Time Rendering Within Tables
    By James F. Kubecki in forum ASP.NET Building Controls
    Replies: 6
    Last Post: November 24th, 02:14 PM
  5. Usage and Benefit of Table Variable
    By CDARS in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 08:04 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139