Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I can see a couple of reasons you need two tables for this: </p> <ul> <li>real employees must have a name, a department etc while forecast employees only may have these attributes</li> <li>there will be responsibilities that only real employees can have, so you want to be able to reference them separately</li> </ul> <p>But at the same time you want to ensure that there is no clash of IDs across the two tables, becausee (hopefully) forecast employees will become actual employee. </p> <p>The way to do this is to implement a super-type/sub-type structure. So you have one table, EMPLOYEES which guarantees single primary keys, and two dependent tables for actual and forecast employees. The use of the type column is crucial, as it ensures that a given employee only appears in one sub-table.</p> <pre><code>create table employees ( emp_id number not null , emp_type varchar2(8) not null , constraint emp_pk primary key (emp_id) , constraint emp_uk unique (emp_id, emp_type) , constraint emp_type_ck check (emp_type in ('FORECAST', 'ACTUAL')); create table actual_employees ( emp_id number not null , emp_type varchar2(8) not null , name varchar2(30) not null , deptno number(2,0) not null , sal number(7,2) not null , hiredate date not null , constraint actemp_pk primary key (emp_id) , constraint actemp_type_ck check (emp_type = 'ACTUAL') , constraint actemp_emp_fk foreign key (emp_id, emp_type) references emp (emp_id, emp_type) deferrable initially deferred ; create table forecast_employees ( emp_id number not null , emp_type varchar2(8) not null , name varchar2(30) , deptno number(2,0) , sal number(7,2) , predicted_joining_date date , constraint foremp_pk primary key (emp_id) , constraint foremp_type_ck check (emp_type = 'FORECAST') , constraint foremp_emp_fk foreign key (emp_id, emp_type) references emp (emp_id, emp_type) deferrable initially deferred ; </code></pre> <p>So the keys might look a bit odd. The parent table has both a primary key and a compound unique key. The primary key guarantees a single instance of the EMP_ID. The unique key allows us to build foreign keys on the child tables which reference both the EMP_ID and the EMP_TYPE. Combined with the check contraints on the child t This is because they reference the unique key on the parent table rather than its primary key. ables this arrangement ensures that an employee can be in either FORECAST_EMPLOYEES or ACTUAL_EMPLOYEES but not both.</p> <p>The foreign keys are deferrable to allow the conversion of forecast employees to actual employess. This requires three activities:</p> <ol> <li>deleting the record from FORECAST_EMPLOYEES</li> <li>inserting a record into ACTUAL_EMPLOYEES</li> <li>changing the EMP_TYPE (but <em>not</em> the EMP_ID) in EMPLOYEES.</li> </ol> <p>Synchronising actions 2 and 3 is easier with deferred constraints.</p> <p>Also, note that other foreign key constraints referencing EMPLOYEES should use the primary key rather than the unique key. If the relationship cares about the type of employee than it probably should link to the child tables instead.</p> <hr> <blockquote> <p>"so bit of headache"</p> </blockquote> <p>Welcome to the world of data modelling. It's one big headache. Because trying to fit messy reality into a clean data model is <em>hard</em>: you need clear requirements to get it right, and an understanding of what matters most so you can make sensible compromises.</p> <p>I proposed a super-type/sub-type approach on the basis of your other question, and because it seems to be the best way handling two sets of data: real employees and notional employees. I think those two groups need to be treated differently. For instance, I would insist on managers being real employees. This is easy to do with an integrity constraint against ACTUAL_EMPLOYEES and way harder to achieve with a single table which contains both types of employees. </p> <p>Sure having two tables means possibly generates more work with regards to synchronising their structures. So what? It's largely trivial, as it is barely more work to write two ALTER TABLE statements than one. Plus it is quite possible that the new column applies only to actual employees and is of no meaning to forecast employees (e.g. EARNED_COMMISSION, LAST_REVIEW_RATING). In that light having separate tables makes the data model more accurate.</p> <p>With regards to having to duplicate dependent tables, as Ollie points out, that is a misunderstanding. Tables which apply to all employees regardless of their actuality shoudl reference the EMPLOYEES table not its children. </p> <p>Finally I don't understand why maintaining historical data is harder with two tables than for one. Most journaling code should be wholly generated from the data dictionary.</p> <hr> <blockquote> <p>"if i have Employee table and Employee_forecast table ... "</p> </blockquote> <p>There are <em>three</em> tables:</p> <ul> <li>EMPLOYEES - a master table to guarantee unique EMP_IDs</li> <li>ACTUAL_EMPLOYEES - a child table for people who work for your company</li> <li>FORECAST_EMPLOYEES - a child table for people who you hope to recruit to your company</li> </ul> <blockquote> <p>"... both of their products or activities will be stored in one single product/ activity table?"</p> </blockquote> <p>Please bear in mind that I am making assumptions about your business logic from the scant details you have provided. </p> <p>Now it seems to me that people who don't yet work for your company should not have any associated activities. In that scenario you would have one table, EMPLOYEE_ACTIVITIES, which is a child of ACTUAL_EMPLOYEES. </p> <p>But perhaps you do genuinely have activities for people who don't exist. So here's a choice: one table or two? The one table design has EMPLOYEE_TASKS as a child of the master EMPLOYEES table. The two table design has ACTUAL_EMPLOYEE_TASKS and FORECAST_EMPLOYEE_TASKS as children of the ACTUAL_EMPLOYEES and FORECAST_EMPLOYEES tables respectively. </p> <p>Which design is the correct one depends on whether you need to enforce rules regarding assignemnt of tasks. For instance, your company might have a rule which states that only real people can hire new staff. So it would be useful to have a model that only allows recruitment tasks to be assigned to ACTUAL_EMPLOYEES. </p> <hr> <blockquote> <p>"this design does not have any considerations for the monthwise forecast"</p> </blockquote> <p>Okay I have added date columns to the two tables. That will allow you to run the report you want.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to design a data model that deals with the current employees and forecasted employees?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAPC
    1. USAPC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI might also have some refrence tables for each of the employee type. Say employee_products. So I will need to have two seprate tables for actual employees and then one more for the forecast employee. If in future I add one more refrence table to actual employee I will also have to add it to forecast table. Plus if ther are some column chnages in actual employee I will have to keep them in sync with the forecast table. Plus the changes on these tables need to be audited as I need to maintain historical data also. so bit of headache if I keep them seperate. What do you suggest?
      singulars
      1. PO
      1. USashishjmeshram
    2. CO@Ashish, if they are just reference tables then link them to the `EMPLOYEE` tables, I'd see no reason to have a reference table specifically per `EMPLOYEE` table as such. If you need to add employee columns then that's a different issue.
      singulars
      1. PO
      1. USOllie
    3. CO@APC, +1 for this...."Welcome to the world of data modelling. It's one big headache" :-)
      singulars
      1. PO
      1. USOllie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload