Mysql

Question

Mysql - flexible, superior structure

I recently inherited an already running project, and now I have one problem. One of the requirements is to allow the user to create a "database" within the application, which can have a variable number of user-defined columns (this is an excellent structure).

Here is sqlfiddle for my current structure.

Here is the query that I use to retrieve strings:

select      `row`, 
            group_concat(dd.value order by field(`col`, 1, 2, 3) asc) as `values`
from        db_record dr,
            db_dictionary dd
where       dr.database_id in (1, 2, 3)
and         dr.database_dictionary_id = dd.id
group by    `row`
order by    group_concat(dd.value order by field(`col`, 1, 2, 3) asc);

The ability to sort by any column is achieved using group_concat ().

I think of this design because I have some doubts about performance and meeting requirements:

It should be sorted (by any column), which means that the user sorts asc by column 2, and the rows are ordered correctly.
/. , .

, , , . - HAVING LIKE, GROUP_CONCAT().

- , ? , , ?

: ? DB :

-------------------------------------------
| database_id | dictionary_id | row | col |
-------------------------------------------
| 1           | 1             | 1   | 1   |
-------------------------------------------
| 2           | 2             | 1   | 2   |
-------------------------------------------
| 3           | 3             | 1   | 3   |
-------------------------------------------

, , : ( 1.. 3 - )

----------------------------------------
| row | column 1 | column 2 | column 3 | 
----------------------------------------
| 1   | 1        | 2        | 3        |
----------------------------------------

mysql? - GROUP_CONCAT(), php ?

, , - , .

+4

sql mysql excel

mkrowiarz 02 . '14 9:56

5

Richard Pascual · Answer 1 · 2014-03-05T04:30:45+0000

Excel-2-MySQL

Excel MySQL

, - MySQL, SQL- DDL DML. db apis . , , .

, . -, "A1" ( A, 1) "G72" ( G, 72)

, .

/-. , .

:

, " ".

A VECTOR by - , , (n) .

: - 2 x 2 , .

,

, , :

/ (, 10045, = "HELEN" )
: (, "A" )

, . Pivot , , ... , !

:

: VARCHAR, , . API , ... , , , - , STRING.

DDL- , , .

DDL, MySQL 5.5.32.

-- First Design Idea... Using a Single Table Solution.

CREATE TABLE DB_VECTOR 
    (
      vid int auto_increment primary key,
      user_id varchar(40),
      row_id int,
      col_id int,
      data_type varchar(10), 
      string_data varchar(500),
      numeric_data int,
      date_data datetime
    );

-- Populate Column A with CITY values

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 2, 1, 'STRING', 'ATLANTA', NULL, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 3, 1, 'STRING', 'MACON', NULL, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 4, 1, 'STRING', 'SAVANNAH', NULL, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 5, 1, 'STRING', 'FORT BENNING', NULL, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 6, 1, 'STRING', 'ATHENS', NULL, NULL);

-- Populate Column B with POPULATION values

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 2, 2, 'NUMERIC', NULL, 1500000, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 3, 2, 'NUMERIC', NULL, 522000, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 4, 2, 'NUMERIC', NULL, 275200, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 5, 2, 'NUMERIC', NULL, 45000, NULL);

INSERT INTO DB_VECTOR (user_id, row_id, col_id, data_type,
  string_data, numeric_data, date_data)
VALUES ('RICHARD', 6, 2, 'NUMERIC', NULL, 1325700, NULL);

, . , (, OWNER/USER ), , .

, . , . , , , , , .
: , , - ... , .
: USER_OWNER , FK , 20 ... CARDINALITY, , .

:

- , , ... , Excel.

    SELECT base_query.CITY, base_query.POPULATION
    FROM (

    SELECT CASE WHEN col_a.data_type = 'STRING'
                THEN col_a.string_data
                WHEN col_a.data_type = 'NUMERIC'
                THEN col_a.numeric_data
                WHEN col_a.data_type = 'DATETIME'
                THEN col_a.date_data ELSE NULL END as CITY,
           CASE WHEN col_b.data_type = 'STRING'
                THEN col_b.string_data
                WHEN col_b.data_type = 'NUMERIC'
                THEN col_b.numeric_data
                WHEN col_b.data_type = 'DATETIME'
                THEN col_b.date_data ELSE NULL END as POPULATION
     FROM db_vector col_a, db_vector col_b
     WHERE ( col_a.col_id = 1 AND col_b.col_id = 2 )
       AND ( col_a.row_id = col_b.row_id)

    ) base_query WHERE base_query.POPULATION >= 500000

    ORDER BY base_query.POPULATION DESC

- . , , -.

: (a.k.a. )

. THREE.
DB_VECTOR ( ) : ( ) .
DB_DATA : STRING_DATA, NUMERIC_DATA DATE_DATA... VID ( ).

VID (, , , ..), .

: "" , , , (, , ..)... .

, , ( ) , , (?) , .

. , DB_VECTOR . , VECTOR_ID (vid), .
UP???: , , : () ... , , ; (b) Excel : (), ( : A ZZ +?) ? - ?
. Excel . ? , . ? , , , ?
.... DB_VECTOR VARCHAR "". catch-bin , (VECTOR ID/POINTER)... , (, "Range Name" Excel)... ?

... . . ... , - .

.

mkrowiarz · Answer 2 · 2014-03-02T22:39:41+0000

, , , , . , , " ", , ( 3 ), :

select      `row`,
            group_concat(if(field(`column`, 1), dd.value, null)) as column1,
            group_concat(if(field(`column`, 2), dd.value, null)) as column2,
            group_concat(if(field(`column`, 3), dd.value, null)) as column3
from        db_record dr
left join   db_dictionary dd on (dr.dictionary_id = dd.id)
where       dr.database_id = 1
group by    `row`
having      column3 like '%biu%'
order by    `columns` asc;

, PHP group_concat(if(...)) HAVING .

, .

Mitch · Answer 3 · 2014-03-05T05:00:54+0000

? ALTER|CREATE|DROP TABLE ad hoc, . SQL .

:

CREATE TABLE Worksheets
(
    WorksheetID int auto_increment primary key,
    WorkbookID int not null,
    Name varchar(256) not null,
    TableName nvarchar(256) not null
);

CREATE TABLE Columns
(
    ColumnID int auto_increment primary key,
    WorksheetID int not null,
    ColumnSequenceNo int not null,
    Name varchar(256) not null,
    PerceivedDatatype enum ('string', 'number') not null
)

-- Example of a dynamically generated data table:
-- Note: The number in the column name would correspond to 
-- ColumnSequenceNo in the Columns table
CREATE TABLE data_e293c71b-b894-4652-a833-ba817339809e
(
    RowID int auto_increment primary key,
    RowSequenceNo int not null,
    Column1String varchar(256) null,
    Column1Numeric double null,
    Column2String varchar(256) null,
    Column2Numeric double null,
    Column3String varchar(256) null,
    Column3Numeric double null,
    -- ...
    ColumnNString varchar(256) null,
    ColumnNNumeric double null
);

INSERT INTO Worksheets (WorkbookID, Name, TableName)
VALUES (1, `Countries`, `data_e293c71b-b894-4652-a833-ba817339809e`);

SET @worksheetID = LAST_INSERT_ID();

INSERT INTO Columns (WorksheetID, ColumnSequenceNo, Name, PerceivedDatatype)
VALUES (@worksheetID, 1, `Country Name`, `string`),
       (@worksheetID, 2, `Population`, `numeric`),
       (@worksheetID, 3, `GDP/person`, `numeric`);

-- example of an insert for a new row:
-- if the new data violates any perceived types, update them first
INSERT INTO data_e293c71b-b894-4652-a833-ba817339809e (
    RowSequenceNo,
    Column1String,
    Column2String, Column2Numeric,
    Column3String, Column3Numeric)
VALUES (
    1,
    `United States of America`,
    `3000000`, 3000000,
    `34500`, 34500);

-- example of a query on the first column:
select * 
from data_e293c71b-b894-4652-a833-ba817339809e 
where Column1String like `United%`;

-- example of a query on a column with a numeric perceived datatype:
select * 
from data_e293c71b-b894-4652-a833-ba817339809e 
where Column3Numeric between 4000 and 40000;

, — .

user8795462 · Answer 4 · 2014-03-11T04:40:53+0000

select      `row`,
            group_concat(if(field(`row`, 1), dd.value, null)) as row1,
            group_concat(if(field(`row`, 2), dd.value, null)) as row2,
            group_concat(if(field(`row`, 3), dd.value, null)) as row3
from        db_record dr
left join   db_dictionary dd on (dr.dictionary_id = dd.id)
where       dr.database_id = 0
group by    `column`
having      row1 like '%biu%'
order by    `row` uni;

Jack · Answer 5 · 2014-03-11T09:32:34+0000

, . , 3 db (). sqlfiddle db_record, .

group_concat , "". sqlfiddle, ""

Using where; Using temporary; Using filesort

" filesort" - fileort. 25 ( , , 2 )

In the original question, creating a "database" inside the "application"? If you mean a flexible database in a database, you are probably abusing a relational database. Try to shift part of the responsibilities to the application level code (php?), Yes outside the database and leave the relational database to perform what is best, to link the corresponding data tables. Keep it simple.

Mysql - flexible, superior structure

Excel-2-MySQL

:

,

:

MySQL

:

: (a.k.a. )

More articles: