Databases
StackOverflow2013
Postsdbo
POSupertype / Subtype Inheritence (Should the number of subtype discriminators equal the number of subtype entities?)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSupertype / Subtype Inheritence (Should the number of subtype discriminators equal the number of subtype entities?)
    primarykey
    data
    text
    <p>[MAJOR EDIT: Thanks Branko for the tip to go with inheritance]</p> <p>BUSINESS (<strong>business_id</strong>)</p> <p>OFFICE (<strong>office_id</strong>, 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; </p> <p>CUBICAL (<strong>cubical_ID</strong>, 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.</p> <p>Entity BUSINESS has many OFFICEs; each OFFICE must belong to one BUSINESS. An OFFICE has many CUBICALs; each CUBICAL must belong to one OFFICE.</p> <hr> <p>A fourth entity, TASK (<strong>task_id</strong>, task_descr), <strong>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.</strong> </p> <p>With Inheritance, I see two ways of doing this with the supertype TASKABLE: either create two subtypes, CUBICAL and NON CUBICAL, with a subtype discriminator of 'cubical' or 'noncubical'; or to create one subtype, CUBICAL, with the subtype discriminator of 'cubical', 'hallway', 'electric room', 'property wide', etc.</p> <hr> <p>TASKABLE (<strong>taskable_id</strong>, taskable_type) where taskable_type in ('cubical', 'non cubical')</p> <p>CUBICAL (<strong>cubical_ID</strong>, office_id, cubical_#, taskable_id)</p> <p>NONCUBICAL (<strong>noncubical_ID</strong>, office_id, descr, taskable_id) <em>where description in ('hallway', 'electric room', 'office wide', 'etc')</em></p> <p>TASK (<strong>task_id</strong>, task_descr, taskable_id)</p> <pre><code>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 </code></pre> <hr> <p>TASKABLE (<strong>taskable_id</strong>, taskable_type) where *taskable_type in ('cubical', 'hallway', 'electric room', 'office wide', 'etc')*</p> <p>CUBICAL (<strong>cubical_ID</strong>, office_id, cubical_#, taskable_id) where taskable_id</p> <p>TASK (<strong>task_id</strong>, taskable_id)</p> <pre><code>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' </code></pre> <hr> <p>Because in this situation the noncubical areas have no other use than to be assigned tasks, and have no other attributes, I figure its best to go with one subtype and use the additional condition on the where clause of the sql statement (NOT LIKE 'cubical')</p> <p>Is it odd to have 5+ subtype discriminators but only use a single subtype entity?</p> <p>Thank you, --Matthew Moisen</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMatthew Moisen
    1. USMatthew Moisen
    plurals
    1. PL
      singulars
      1. LTLinked
    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. POSupertype / Subtype Inheritence (Should the number of subtype discriminators equal the number of subtype entities?)
      1. USMatthew Moisen
      1. VTFavorite
    1. COYour best bet may be to create a cubical called "common". Each task has a cubicle. Get the business_id out of the task table.
      singulars
      1. POSupertype / Subtype Inheritence (Should the number of subtype discriminators equal the number of subtype entities?)
      1. USEdwin
    2. CO@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?
      singulars
      1. POSupertype / Subtype Inheritence (Should the number of subtype discriminators equal the number of subtype entities?)
      1. USMatthew Moisen
    3. CO0 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.
      singulars
      1. POSupertype / Subtype Inheritence (Should the number of subtype discriminators equal the number of subtype entities?)
      1. USEdwin
 

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