Best way to combine many disparate schemas for database table creation?

I have a bunch of data that consists of public records from the state government dating back to the early 90s. Along the way, the data organization and attributes have changed significantly. I put together an Excel sheet containing the headers in each year’s file to make sense of it and it came out like this:

enter image description here

As you can see by looking at my checksum colum on the left, there are 8 different schemas from 1995 through 2019. Also, you can see that the data between each can vary quite a bit. I’ve color-coded columns that are logically similar. Sometimes the data is mostly the same but the names of the columns have changed. Sometimes, there is different data altogether that appears or disappears.

I think it is pretty clear that the best goal here is to have 1 table combining all of this information rather than 8 disparate tables, since I want to be able to query across all of them efficiently. Each table contains ~150,000 rows so the table would have around 4 million records. Each table has 55-60 fields approximately.

I’ve been struggling for a few days with how to tackle it. Half of the files were fixed-width text files, not even CSVs, so it took me a long time to properly convert those. The rest are thankfully already CSVs or XLSX. From here, I would like to end up with a table that:

  • includes a superset of all available logically distinct columns – meaning that the ID number and ID Nbr columns would be the same in the final table, not 2 separate tables
  • has no loss of data

Additionally, there are other caveats such as:

  • random Filler columns (like in dark red) that serve no purpose
  • No consistency with naming, presence/absence of data, etc.
  • data is heavily denormalized but does not need to be normalized
  • there’s a lot of data, 2 GB worth just as CSV/XLS/XLSX files

I basically just want to stack the tables top to bottom into one big table, more or less.

I’ve considered a few approaches:

  • Create a separate table for each year, import the data, and then try to merge all of the tables together
  • Create one table that contains a superset of the columns and add data to it appropriately
  • Try pre-processing the data as much as possible until I have one large file with 4 million rows that I can convert into a database

I’ve tried importing just the first table into both SQL Server and Access but have encountered issues there with their inability to parse the data (e.g. duplicate columns, flagging columns with textual data as integers). In any case, it’s not practical to manually deal with schema issues for each file. My next inclination was to kind of patchwork this together in Excel, which seems the most intuitive, but Excel can’t handle a spreadsheet that large so that’s a no-go as well.

The ultimate goal is to have one large (probably multi-GB) SQL file that I can copy to the database server and run, maybe using LOAD IN FILE or something of that sort – but with the data all ready to go since it would be unwieldy to modify afterwards.

Which approach would be best? Additionally, what tools should I be using for this? Basically the problem is trying to "standardize" this data with a uniform schema without losing any data and being as non-redundant as possible. On the one hand, it doesn’t seem practical to go through all 25 tables manually and try to get them imported or try to change the schema on each one. I’m also not sure about trying to figure out the schema now and then modifying the data, since I can’t work with it all at once? Any advice from people who have done stuff like this before? Much appreciated!

Can a FOREIGN KEY be referring to the same table as the “source”?

I was sure that I had done this countless times, but I can’t find any previous use of me doing this. But it seems to make perfect sense, so I assume that it must be possible? However, the manual says:

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.

Emphasis mine. Source: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK

I have a table for bookkeeping, and whenever a transaction "corrects" or "updates" a previous one, I have a column called "corrects id" which is a bigint and, to me, makes perfect sense to have as a FOREIGN KEY as such:

..., FOREIGN KEY ("corrects id") REFERENCES bookkeeping (id) 

Note: "corrects id" is a column in the "bookkeeping" table, and id is the primary key (bigserial) of the same table.

Surely this is correct? So why does the manual say that it has to be another table? And why is the name "FOREIGN" keys if you can refer to the same table?

Create dynamic sql query to select all related data in DB based on entry table and ID

Hope all is well. I am hoping you can help me.

Problem Statement – I’m tasked to create a dynamic SQL statement which will select all related data from a given table where the Identifier is passed. For each table where the relevant data is found i would like the data to be exported onto a separate tab of within excel

If i was doing this manually done this i would perform the following queries and export the data onto each tab;

Select * from  Mason where id = 12345 Select * from  MasonContacts where Companyid= 12345  Select * from  MasonOpportunities  where Comid = 12345  

However given the sheer volume of tables this isn’t viable.

Step 1 : Type in my identifier (in this case my identifier is a field called "Id" in the Table "Mason") The query will always start from this table.

Table Name : Mason Field : Id = "12345"

Step 2 : Search against table "MasonContacts", search against the field "Companyid". Return all columns & records where the field "Companyid = 12345"

Table Name : MasonContacts Field : Companyid

Step 3 : Search against table "Mason Opportunities ", search against the field "Comid". Return all columns & records where the field "Company = 12345"

Table Name : MasonOpportunities Field : Comid

Looking forward to your help

Display `Table` output as a 3D bar chart

Create a simple function with arguments {i,j}, and make a Table for an array of values. For example:

f[i_, j_] := i*j;  data = Table[f[i, j], {i, 1, 25}, {j, 1, 25}] 

I want to create a 3D ‘bar chart’ of data – i.e., a plot that has a column of height f[i, j] at each Cartesian point {i,j} (ideally a block-shaped column with width and depth 1 centred on {i,j}. It sounds dead simple…

But ListPlot3D creates a meshed surface, not a series of columns, and I can’t see an option to change this. And Histogram3D doesn’t accept the format of data, and in any case I can’t get my head around how it generates 3D information from pairs containing only 2 data points each – it’s set up for data sampling, not plotting.

Suggestions?

Help with Many to Many Table Data Entry

I’ve got a question on how to "prettify" data entry and removal for a many to many relationship.

Imagine if you will a DB with 3 tables. Tables called Users, Roles, and RoleAssignment.

-- `Users`     CREATE TABLE `Users` (  `userID`   int NOT NULL AUTO_INCREMENT ,  `userName` varchar(40) NOT NULL ,  `realName` varchar(40) NOT NULL ,  PRIMARY KEY (`userID`), UNIQUE KEY `username` (`username`) ) AUTO_INCREMENT=1;   -- `Roles` CREATE TABLE `Roles` (  `roleID`       int AUTO_INCREMENT NOT NULL ,  `roleName`     VARCHAR(40) NOT NULL ,  PRIMARY KEY (`roleID`) );   -- `RoleAssignment`  CREATE TABLE `RoleAssignment` (  roleAssignID int NOT NULL AUTO_INCREMENT ,  roleID       int NOT NULL ,  userID       int NOT NULL ,  PRIMARY KEY (roleAssignID), FOREIGN KEY (userID) REFERENCES Users(userID), FOREIGN KEY (roleID) REFERENCES Roles(roleID) ) AUTO_INCREMENT=1; 

This is a database where RoleAssignment is essentially a child table only meant to connect two Parent tables together. I made them this way because the user<->role relationship is many to many.

I found a way of getting this to output just relationships between users and their roles… Both queries output the same data but query 2 is better organized.

SELECT     Users.userName,     Users.realName,     Roles.roleName FROM Users JOIN RoleAssignment ON Users.userID = RoleAssignment.userID JOIN Roles ON Roles.roleID = RoleAssignment.roleID;  SELECT Users.userName, Users.realName, GROUP_CONCAT(Roles.roleName) FROM Users JOIN RoleAssignment ON Users.userID = RoleAssignment.userID JOIN Roles ON Roles.roleID = RoleAssignment.roleID GROUP BY Users.userID; 

Is there an easy way to add an entry to the RoleAssignment table by name instead of ID in a single query? i.e. "Add user jack to role moderator"? Or would I need to write a script to find the ID of the user, then the ID of the role, and finally add an entry to RoleAssignment?

Any help or guidance would be appreciated!

Changing the Shopping cart Table order / VAT disappeared

I want to change the column order in the Shopping cart table. I want to exchange the Posititions of the Sum and VAT Column so that the Sum is the Last position. I guest the right php Template is the cart.php. Also the VAT name disappeared somehow in my column. I have VAT activated in all of my plugins and settings but it wont show :S I would be really thankful if the Pros out there could help me 🙂

enter image description here

    <?php /**  * Cart Page  *  * This template can be overridden by copying it to yourtheme/woocommerce/cart/cart.php.  *  * HOWEVER, on occasion WooCommerce will need to update template files and you  * (the theme developer) will need to copy the new files to your theme to  * maintain compatibility. We try to do this as little as possible, but it does  * happen. When this occurs the version of the template file will be bumped and  * the readme will list any important changes.  *  * @see     https://docs.woocommerce.com/document/template-structure/  * @package WooCommerce/Templates  * @version 3.8.0  */  defined( 'ABSPATH' ) || exit;  do_action( 'woocommerce_before_cart' ); ?>  <form class="woocommerce-cart-form" action="<?php echo esc_url( wc_get_cart_url() ); ?>" method="post">     <?php do_action( 'woocommerce_before_cart_table' ); ?>      <table class="shop_table shop_table_responsive cart woocommerce-cart-form__contents" cellspacing="0">         <thead>             <tr>                 <th class="product-remove">&nbsp;</th>                 <th class="product-thumbnail">&nbsp;</th>                 <th class="product-name"><?php esc_html_e( 'Product', 'woocommerce' ); ?></th>                 <th class="product-price"><?php esc_html_e( 'Price', 'woocommerce' ); ?></th>                 <th class="product-quantity"><?php esc_html_e( 'Quantity', 'woocommerce' ); ?></th>                 <th class="product-subtotal"><?php esc_html_e( 'Subtotal', 'woocommerce' ); ?></th>             </tr>         </thead>         <tbody>             <?php do_action( 'woocommerce_before_cart_contents' ); ?>              <?php             foreach ( WC()->cart->get_cart() as $  cart_item_key => $  cart_item ) {                 $  _product   = apply_filters( 'woocommerce_cart_item_product', $  cart_item['data'], $  cart_item, $  cart_item_key );                 $  product_id = apply_filters( 'woocommerce_cart_item_product_id', $  cart_item['product_id'], $  cart_item, $  cart_item_key );                  if ( $  _product && $  _product->exists() && $  cart_item['quantity'] > 0 && apply_filters( 'woocommerce_cart_item_visible', true, $  cart_item, $  cart_item_key ) ) {                     $  product_permalink = apply_filters( 'woocommerce_cart_item_permalink', $  _product->is_visible() ? $  _product->get_permalink( $  cart_item ) : '', $  cart_item, $  cart_item_key );                     ?>                     <tr class="woocommerce-cart-form__cart-item <?php echo esc_attr( apply_filters( 'woocommerce_cart_item_class', 'cart_item', $  cart_item, $  cart_item_key ) ); ?>">                          <td class="product-remove">                             <?php                                 echo apply_filters( // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped                                     'woocommerce_cart_item_remove_link',                                     sprintf(                                         '<a href="%s" class="remove" aria-label="%s" data-product_id="%s" data-product_sku="%s">&times;</a>',                                         esc_url( wc_get_cart_remove_url( $  cart_item_key ) ),                                         esc_html__( 'Remove this item', 'woocommerce' ),                                         esc_attr( $  product_id ),                                         esc_attr( $  _product->get_sku() )                                     ),                                     $  cart_item_key                                 );                             ?>                         </td>                          <td class="product-thumbnail">                         <?php                         $  thumbnail = apply_filters( 'woocommerce_cart_item_thumbnail', $  _product->get_image(), $  cart_item, $  cart_item_key );                          if ( ! $  product_permalink ) {                             echo $  thumbnail; // PHPCS: XSS ok.                         } else {                             printf( '<a href="%s">%s</a>', esc_url( $  product_permalink ), $  thumbnail ); // PHPCS: XSS ok.                         }                         ?>                         </td>                          <td class="product-name" data-title="<?php esc_attr_e( 'Product', 'woocommerce' ); ?>">                         <?php                         if ( ! $  product_permalink ) {                             echo wp_kses_post( apply_filters( 'woocommerce_cart_item_name', $  _product->get_name(), $  cart_item, $  cart_item_key ) . '&nbsp;' );                         } else {                             echo wp_kses_post( apply_filters( 'woocommerce_cart_item_name', sprintf( '<a href="%s">%s</a>', esc_url( $  product_permalink ), $  _product->get_name() ), $  cart_item, $  cart_item_key ) );                         }                          do_action( 'woocommerce_after_cart_item_name', $  cart_item, $  cart_item_key );                          // Meta data.                         echo wc_get_formatted_cart_item_data( $  cart_item ); // PHPCS: XSS ok.                          // Backorder notification.                         if ( $  _product->backorders_require_notification() && $  _product->is_on_backorder( $  cart_item['quantity'] ) ) {                             echo wp_kses_post( apply_filters( 'woocommerce_cart_item_backorder_notification', '<p class="backorder_notification">' . esc_html__( 'Available on backorder', 'woocommerce' ) . '</p>', $  product_id ) );                         }                         ?>                         </td>                          <td class="product-price" data-title="<?php esc_attr_e( 'Price', 'woocommerce' ); ?>">                             <?php                                 echo apply_filters( 'woocommerce_cart_item_price', WC()->cart->get_product_price( $  _product ), $  cart_item, $  cart_item_key ); // PHPCS: XSS ok.                             ?>                         </td>                          <td class="product-quantity" data-title="<?php esc_attr_e( 'Quantity', 'woocommerce' ); ?>">                         <?php                         if ( $  _product->is_sold_individually() ) {                             $  product_quantity = sprintf( '1 <input type="hidden" name="cart[%s][qty]" value="1" />', $  cart_item_key );                         } else {                             $  product_quantity = woocommerce_quantity_input(                                 array(                                     'input_name'   => "cart[{$  cart_item_key}][qty]",                                     'input_value'  => $  cart_item['quantity'],                                     'max_value'    => $  _product->get_max_purchase_quantity(),                                     'min_value'    => '0',                                     'product_name' => $  _product->get_name(),                                 ),                                 $  _product,                                 false                             );                         }                          echo apply_filters( 'woocommerce_cart_item_quantity', $  product_quantity, $  cart_item_key, $  cart_item ); // PHPCS: XSS ok.                         ?>                         </td>                          <td class="product-subtotal" data-title="<?php esc_attr_e( 'Subtotal', 'woocommerce' ); ?>">                             <?php                                 echo apply_filters( 'woocommerce_cart_item_subtotal', WC()->cart->get_product_subtotal( $  _product, $  cart_item['quantity'] ), $  cart_item, $  cart_item_key ); // PHPCS: XSS ok.                             ?>                         </td>                     </tr>                     <?php                 }             }             ?>              <?php do_action( 'woocommerce_cart_contents' ); ?>              <tr>                 <td colspan="6" class="actions">                      <?php if ( wc_coupons_enabled() ) { ?>                         <div class="coupon">                             <label for="coupon_code"><?php esc_html_e( 'Coupon:', 'woocommerce' ); ?></label> <input type="text" name="coupon_code" class="input-text" id="coupon_code" value="" placeholder="<?php esc_attr_e( 'Coupon code', 'woocommerce' ); ?>" /> <button type="submit" class="button" name="apply_coupon" value="<?php esc_attr_e( 'Apply coupon', 'woocommerce' ); ?>"><?php esc_attr_e( 'Apply coupon', 'woocommerce' ); ?></button>                             <?php do_action( 'woocommerce_cart_coupon' ); ?>                         </div>                     <?php } ?>                      <button type="submit" class="button" name="update_cart" value="<?php esc_attr_e( 'Update cart', 'woocommerce' ); ?>"><?php esc_html_e( 'Update cart', 'woocommerce' ); ?></button>                      <?php do_action( 'woocommerce_cart_actions' ); ?>                      <?php wp_nonce_field( 'woocommerce-cart', 'woocommerce-cart-nonce' ); ?>                 </td>             </tr>              <?php do_action( 'woocommerce_after_cart_contents' ); ?>         </tbody>     </table>     <?php do_action( 'woocommerce_after_cart_table' ); ?> </form>  <?php do_action( 'woocommerce_before_cart_collaterals' ); ?>  <div class="cart-collaterals">     <?php         /**          * Cart collaterals hook.          *          * @hooked woocommerce_cross_sell_display          * @hooked woocommerce_cart_totals - 10          */         do_action( 'woocommerce_cart_collaterals' );     ?> </div>  <?php do_action( 'woocommerce_after_cart' ); ?> 

Table Filtering Using Big List

Briefly, I am trying to filter a table using conditions plus a list of items in Oracle. In detail, I have a reporting table which has millons of records. I would like to filter it using external lists which might include more than 1000 lines of fields from the record table. (Obviously, I especially said 1000, because in clause in Oracle has the limit of 1000 items). So my question is; How can I effectively filter a huge table using another big list.

Generate all combinations for variables and insert into temp table

0

I have one requirement there are 4 variables and i want all combinations of 4 variables and insert into temp table .

DECLARE StartDateTime DATETIME; DECLARE Age INT;  DECLARE Duration INT ; DECLARE TotalDD INT;  CREATE TEMPORARY TABLE tempTable(     Duration INT,     TotalDD INT,     Age INT,     StartDateTime DATETIME,     ); SET Age = 16; SET TotalDD = 14; SET Duration = 30; SET StartDateTime = CURDATE(); 

Excepted Result:

Duration age TotalDD StartDateTime 30 null null null null 16 null null 30 null null null 30 16 null null null null 14 20200622 30 null 14 null 30 16 null 20200622 

……….. ………. so on