1

BUSINESS (business_id)

OFFICE (office_id, business_id, office_name) office_id is a sequence for the PK; business_id and office_name are a composite unique key (the business key); business_id is a mandatory FK to BUSINESS;

CUBICAL (cubical_ID, office_id, cubical_#) cubical_ID is sequence for the PK; office_id and cubical_# are a composite unique key (the business key); office_id is a mandatory FK to OFFICE.

Entity BUSINESS has many OFFICEs; each OFFICE must belong to one BUSINESS. An OFFICE has many CUBICALs; each CUBICAL must belong to one OFFICE.

A fourth entity, TASK (task_id, task_descr), can be assigned to cubicals but also to areas within the office that serve no purpose for the business aside from having TASKs applied to them, for example a hallway, electric room, or the whole office.

With inheritance, I see two ways of doing this with the supertype TASKABLE: either create two subtypes, CUBICAL and NON CUBICAL, with subtype discriminator 'cubical' or 'noncubical', or create one subtype, CUBICAL, with subtype discriminator one of 'cubical', 'hallway', 'electric room', 'property wide', etc.

TASKABLE (taskable_id, taskable_type) where taskable_type in ('cubical', 'non cubical')

CUBICAL (cubical_ID, office_id, cubical_#, taskable_id)

NONCUBICAL (noncubical_ID, office_id, descr, taskable_id) *where description in ('hallway', 'electric room', 'office wide', 'etc')

TASK (task_id, task_descr, taskable_id)

select cubical_#, task_descr
from cubical, task
where cubical.taskable_id = taskable.taskable_id
union
select non_cubical.descr, task_descr
from noncubical, task
where noncubical.taskable_id = taskable.taskable_id

TASKABLE (taskable_id, taskable_type) where *taskable_type in ('cubical', 'hallway', 'electric room', 'office wide', 'etc')

CUBICAL (cubical_ID, office_id, cubical_#, taskable_id) where taskable_id

TASK (task_id, taskable_id)

select cubical_#, task_descr
from cubical, task
where cubical.taskable_id = task.taskable_id
union
select taskable.taskable_type, task.task_descr
from taskable, task_descr
where taskable.taskable_id = task.task_id
    and taskable.taskable_type NOT LIKE 'cubical'

Because the noncubical areas have no other use than to be assigned tasks, and have no other attributes, it's best to go with one subtype and use the additional condition on the where clause (NOT LIKE 'cubical').

Is it odd to have 5+ subtype discriminators but only use a single subtype entity?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Matthew Moisen
  • 16,701
  • 27
  • 128
  • 231
  • Your best bet may be to create a cubical called "common". Each task has a cubicle. Get the business_id out of the task table. – Edwin Jan 21 '13 at 00:56
  • @Edwin Good point. There is no need to force the cubical_# field in CUBICAL to be a number as it won't be used in math; it can be a varchar2. However, if that field needs to be used in an order clause for whatever reason, is it wise to establish the value of 0 as the "common", and then tell the developers to follow that convention? Or is it better to place another field in cubical, such as cubical_or_common? – Matthew Moisen Jan 21 '13 at 01:03
  • 0 sounds good. This example is like a cash or walk-in account for a sales oriented business. No one gets a commission when a new customer walks into the store and buys a widget with cash. It's okay for the cash account to show up in the reports. – Edwin Jan 21 '13 at 01:10
  • @Edwin Ok. How about this: what if there is possible a cleaning room, a maintenance room, an electric room, and other unknown rooms depending on the office, inside the office. The sole use they would have in regards to the ERD is that a task can be applied to them. In this instance, is it best to add a column to CUBICAL named "cubical_use_id" and have it as a FK to another table named CUBICAL_USE (**cubical_use_id**, office_id, cubical_use_code), where cubical_use_id is a seq/pk, and office_id and cubical_use_code is a composite unique business key, to allow each office to establish their own? – Matthew Moisen Jan 21 '13 at 01:31
  • and actually maybe another column in CUBICAL_USE_CODE called cubical_#, to keep track of how that particular office has decided to number their extra nonessential rooms? And then each office that is created would automatically have a row inserted in cubical use as (seq.nextval, (select office_id where...), 'NORMAL', null) and then all cubicals will use the NORMAL code. – Matthew Moisen Jan 21 '13 at 01:35
  • There are 3 main strategies to deal with these kinds of structures, as discussed in ["DB Design for Choosing One of Multiple Possible Foreign Tables"](http://stackoverflow.com/a/14409433/533120). – Branko Dimitrijevic Jan 21 '13 at 10:29
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Feb 14 '23 at 23:15

0 Answers0