Re: Complex Database Creation
Posted by: Peter Brawley
Date: July 31, 2021 10:34AM

Sorry, SQL is an incomplete computer language, not designed to treat column names as data---for example it has no way to efficiently query shift numbers or day numbers when they are coded into column names like Shift2Day5NumEmpBusBusies &c. That just will not fly.

A table like "first table" fails too many normalisation rules (eg see https://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf) to be efficient, queryable, or even usable.

A table like this needs to be decomposed at least to Third Normal Form. Here, that'd entail at least a parent shifts table with child tables shift, day and a bridge shift_day table. All those shift and day references you've coded into column names need to become row specifications in child tables.

That is, your table layout will look like ...

business
  section
    employees
      dayofweek
        shift
          ... &c ...

such that each table references the table directly above it in the scheme via a foreign key.

Once that's in place, you try to translate each job specified by the requirement---for example assign employee E in section S in business B to particular shifts and days---into a Select, Insert, Delete or Update query. At any point in data design development, either the table design allows the query, or it does not. If it does not, you modify the design accordingly, continuing this process till the data design supports every required job & query.



Edited 2 time(s). Last edit at 08/01/2021 09:03AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: Complex Database Creation
July 31, 2021 10:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.