> 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
> 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
> 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
> >> 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));
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!