Design: one table or two tables, depending on usage

[Bob]

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$1@duster.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?
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

[Donald Halloran]

"Bob" <bob@nospam.com> wrote in message
news:uo2s$ppQDHA.2096@TK2MSFTNGP12.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]

"Bob" <bob@nospam.com> wrote in message
news:OohfC4qQDHA.2832@TK2MSFTNGP10.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]

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$1@duster.adelaide.on.net...

>
> "Bob" <bob@nospam.com> wrote in message
> news:OohfC4qQDHA.2832@TK2MSFTNGP10.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.
>
>

[Joe Celko]

>> 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]

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!

[Donald Halloran]

"Joe Celko" <anonymous@devdex.com> wrote in message
news:uE%23xZN0QDHA.1072@TK2MSFTNGP10.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).

[Bob]

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" <anonymous@devdex.com> wrote in message
news:ODZqlY0QDHA.3664@tk2msftngp13.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!

[Joe Celko]

>> 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!

[Donald Halloran]

"Joe Celko" <anonymous@devdex.com> wrote in message
news:%23biClsARDHA.2228@tk2msftngp13.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]

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]

"Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
news:3f08cdfd$1@duster.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]

"Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
news:3f08cf2f$1@duster.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)
)


:)

[--CELKO--]

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.

[Donald Halloran]

"--CELKO--" <71062.1056@compuserve.com> wrote in message
news:c0d87ec0.0307081519.469ca180@posting.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]

"Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
news:3f0be1f6@duster.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.