user behavior – How to design a parent child relation using vuetify table?

I have to do a crud example for my parent table(this part is ok),i have used vuetify datatable,but for each parent element i should display all childs ,then should do the all operations like create,update,delete for all the childs.As my idea is to add a btn in action columns which will redirect me to another page with all the childs displayed .
Is there any other suggestions to help how to design it?
Thank u ,
Nelida

mariadb – why timezone like ‘+00:00’ ignored with select but error with create table?

I am wondering, why does mariadb ignore timezone like ‘+00:00’ with select statement however throw the error with create table statement?

SELECT CAST('2020-07-09 09:55:36.933554+00:00' AS DATE) dt;

# dt 
2020-07-09

CREATE TEMPORARY TABLE tmp AS 
SELECT CAST('2020-07-09 09:55:36.933554+00:00' AS DATE) dt;

# (1292): Truncated incorrect datetime value: '2020-07-09 09:55:36.933554+00:00'

I read the question about the error and understand how to fix it. Just curious why such not obvious for me behaviour.

mariadb – 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!

postgresql – 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?

postgresql – Unable to create view with temp table

Hi I am working on one project and want to create a view. I am using temp table in my query. can any one let me know how we can create such view ?

Following is my query, I tried using WITH..AS , but it gives due to insert query.

create temporary table temp_testtbl as
select id,dte,name,hrs_total
FROM dttbl.dts limit 0;

insert into temp_testtbl 
select id,dte,name,hrs_total
FROM dttbl.dts 
group by id,dte,name,hrs_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.hrs_total) AS myhrs
   FROM temp_testtbl i
   JOIN schema1.mytable idm ON idm.src_id = i.iex_id::text AND idm.src_type_id = 123 AND idm.end_dte IS NULL
   JOIN schema1.mytable idm2 ON idm2.empl_id = idm.empl_id AND idm2.src_type_id = 345 AND idm2.end_dte IS NULL
  GROUP BY i.dte, idm.empl_id limit 100;

Correct column order of comparison data table

I need to design a table where the user will need to approve new values. The table will show him two old values and the gap between the old ones and the new one (The percentage represent the gap from the old value to the new value), if he’ll see a suspicious percentage gap he will check the amounts.

Do you have an idea of how to sort the columns for efficient scanning and so it will be clear what the percentage represents?

Table design example

web app – Data Table and List

Hope you all are safe.

I am designing a selection list for one of the requirement in my project. Preferably, like shown below

enter image description here

I wanted to know what is the difference between List and Data table to achieve this.
Anything in particular that there are some limitations that I need to consider about? Feel free to shoot the answer directly.

Please note: The interaction with user is only selection and unselect.

database design – How to structure a listings table

Say I have a table called listing and it has the following columns:

id | name   | description | price | created_date|
 0 | iPhone | A phone     | 1000  |   7/4/2020  |

Since I want to track when a listing is sold, I’m considering adding a sold_date column.

My concern is that this would cause a bunch of rows in the listing table to have a null in the sold_date column since every listing wouldn’t be considered sold. Would having null values be okay in this case, or should I create another table that would contain the sold_date column and references a listing?

How to generated scripts of all table with there all type of indexes present for table in MS SQL Server?

I want to generate a scripts of all the tables(Create).

From database -> Task -> Generate script->Select Specific database objects -> Tables.

Get only table create with primary key & foreign key script. Clustered, Non Clustered Indexes, Full-Text Indexes are not present in generated script.

entities – Base table or view not found: 1146 Table ‘drupal8_formation.profiles’ doesn’t exist

i have this following error when i want to get access to the module ‘profile’:

SQLSTATE(42S02): Base table or view not found: 1146 Table ‘mydatabase.profile’ doesn’t exist: SELECT base_table.profile_id AS profile_id, base_table.profile_id AS base_table_profile_id FROM {profiles} base_table GROUP BY base_table.profile_id LIMIT 1 OFFSET 0; Array ( ) dans DrupalCoreEntityEntityStorageBase->hasData() (ligne 580 de /var/www/html/core/lib/Drupal/Core/Entity/EntityStorageBase.php).

can anyone help me please ?


<?php

namespace DrupalprofileEntity;

use DrupalCoreEntityContentEntityBase;
use DrupalCoreEntityEntityTypeInterface;
use DrupalCoreFieldBaseFieldDefinition;

/**
 * Defines the user entity class.
 *
 *
 * @ContentEntityType(
 *   id = "profile",
 *   label = @Translation("Profile"),
 *   handlers = {
 *     "list_builder" = "DrupalprofileProfileListBuilder",
 *     "route_provider" = {
 *       "html" = "DrupalCoreEntityRoutingDefaultHtmlRouteProvider",
 *     },
 *     "form" = {
 *       "default" = "DrupalprofileFormProfileEntityForm",
 *       "add" = "DrupalprofileFormProfileEntityForm",
 *       "edit" = "DrupalprofileFormProfileEntityForm",
 *       "delete" = "DrupalprofileFormProfileEntityDeleteForm",
 *     },
 *   },
 *   admin_permission = "administer",
 *   base_table = "profile",
 *   data_table = "profile_field_data",
 *   translatable = TRUE,
 *   entity_keys = {
 *     "id" = "profile_id",
 *     "label" = "last_name",
 *     "langcode" = "langcode",
 *     "uuid" = "uuid"
 *   },
 *   links = {
 *     "add-form" = "/profile/add",
 *     "canonical" = "/profile/{profile}",
 *     "edit-form" = "/profile/{profile}/edit",
 *     "delete-form" = "/admin/profile/{profile}/delete",
 *     "collection" = "/admin/profile",
 *   },
 * )
 */

/*
    Creation of the Profile entity class
*/
class Profile extends ContentEntityBase implements ProfileInterface {

  public static function baseFieldDefinitions(EntityTypeInterface $entity_type) {

    $fields('profile_id') = BaseFieldDefinition::create('integer')
        ->setLabel('Profile ID')
        ->setReadOnly(TRUE)
        ->setSetting('unsigned', TRUE);

    $fields('langcode') = BaseFieldDefinition::create('language')
        ->setLabel('Language code');

    $fields('uuid') = BaseFieldDefinition::create('uuid')
        ->setLabel('UUID')
        ->setReadOnly(TRUE);

   // Field Gender
   $fields('gender') = BaseFieldDefinition::create('list_string')
      ->setLabel(t('Gender'))
      ->setDescription(t('The gender of the Profile entity.'))
      ->setSettings(array(
        'allowed_values' => array(
          'female' => 'female',
          'male' => 'male',
        ),
      ))
      ->setDisplayOptions('view', array(
        'label' => 'above',
        'type' => 'string',
        'weight' => -4,
      ))
      ->setDisplayOptions('form', array(
        'type' => 'options_select',
        'weight' => -4,
      ))
      ->setDisplayConfigurable('form', TRUE)
      ->setDisplayConfigurable('view', TRUE);
     // Field Firsnt_name
    $fields('last_name') = BaseFieldDefinition::create('string')
      ->setLabel(t('Last Name'))
      ->setDescription(t('The last name of the Profile entity.'))
      ->setSettings(array(
        'default_value' => '',
        'max_length' => 255,
        'text_processing' => 0,
      ))
      ->setDisplayOptions('view', array(
        'label' => 'above',
        'type' => 'string',
        'weight' => -5,
      ))
      ->setDisplayOptions('form', array(
        'type' => 'string',
        'weight' => -5,
      ))
      ->setDisplayConfigurable('form', TRUE)
      ->setDisplayConfigurable('view', TRUE);

     // Field First name
    $fields('first_name') = BaseFieldDefinition::create('string')
      ->setLabel(t('First Name'))
      ->setDescription(t('The first name of the Profile entity.'))
      ->setSettings(array(
        'default_value' => '',
        'max_length' => 255,
        'text_processing' => 0,
      ))
      ->setDisplayOptions('view', array(
        'label' => 'above',
        'type' => 'string',
        'weight' => -5,
      ))
      ->setDisplayOptions('form', array(
        'type' => 'string',
        'weight' => -5,
      ))
      ->setDisplayConfigurable('form', TRUE)
      ->setDisplayConfigurable('view', TRUE);
     // Field Birth Date
    $fields('birth_date') = BaseFieldDefinition::create('datetime')
      ->setLabel(t('Birth Date'))
      ->setDescription(t('The Birth DAte of the Profile entity.'))
      ->setSetting('datetime_type','date')
      ->setRequired(TRUE)
      ->setDisplayOptions('view', array(
        'label' => 'above',
        'type' => 'string',
        'weight' => -5,
      ))
      ->setDisplayConfigurable('form', TRUE)
      ->setDisplayConfigurable('view', TRUE);
     // Field Birth Date
    $fields('email') = BaseFieldDefinition::create('email')
      ->setLabel(t('Email'))
      ->setDescription(t('Your email.'))
      ->setRequired(TRUE)
      ->setDisplayOptions('view', array(
        'label' => 'above',
        'type' => 'string',
        'weight' => -5,
      ))
      ->setDisplayOptions('form', array(
        'type' => 'string',
        'weight' => -5,
      ))
      ->setDisplayConfigurable('form', TRUE)
      ->setDisplayConfigurable('view', TRUE);
    // Entity Created
    $fields('created') = BaseFieldDefinition::create('created')
      ->setLabel(t('Created'))
      ->setDescription(t('The time that the entity was created.'));
    // Entity Changed
    $fields('changed') = BaseFieldDefinition::create('changed')
      ->setLabel(t('Changed'))
      ->setDescription(t('The time that the entity was last edited.'));

    return   $fields;
  }
}