![]() Pivot outputs could be accomplished generically with probably some simple matrix algebra transformations. Your data set would be all vector id's and their associated data values which have these common values. Database/Spreadsheet ID and Owner (Example, 10045, Owner = 'HELEN').To build search queries from the data values of vectors that have common attributes such as: The solution is scalable: mini-databases can be as small as 2 rows x 2 columns or hundreds to thousands of rows and columns wide. Defining the Smallest Schema Unit: The VectorĪfter bundling together all the identifying meta info about each data point, the collection is now tagged with a single, globally unique ID, which to some may now appear like a catalog of " vectors".Ī VECTOR by mathematical definition is a collection of multiple components and their values used to simplify solutions for problems which exist in spaces that are described through multiple (n) dimensions. For a multi-user environment, the same schema can still be used by adding a supporting user ID value to associate with each spreadsheet ID. This walk-through demonstration will show how to adapt the following data sample in spreadsheet form into a reusable, multi-user relational database format.Ī pair of coordinates can also include a uniquely assigned spreadsheet/mini-db ID value. The simplest combination would be a row-column coordinate pair, such as "A1" (Column A, Row Number 1) or "G72" (Column G, Row Number 72) The schema leverages an assumption that each data point on the spreadsheet grid can be represented by a unique combination of keys. Schema Overview and Design Concepts to Adapt a Spreadsheet The focus of this walk through is the purpose of the schema design, organization of the data and supporting values as well as potential points of expansion for additional enhancements. The maintenance of this database can be handled through a combination of internal db constraints and stored procedure apis, or externally by an alternate scripting language and user interface. The approach of this solution may work for other relational database systems as it does not rely on any specific features of MySQL except for SQL compliant DDL and DML commands. ![]() I need a flexlible and efficient structure, and I hope someone can advise me on that, I would really appreciate any help or suggestions.Ī Flexible, Dynamic Adaption of Excel Format to a MySQL Relational Schema Is that achievable in mysql? Or the only solution is to use GROUP_CONCAT() and then I can use php to split into columns? | database_id | dictionary_id | row | col |Īnd I would like to get a query result groupped by row, similar to that: (column 1. Last question, is it possible to return values for each column in one query? In DB, records look like this:. ![]() Second one - I also tried adding HAVING clause to the query with LIKE, but it compared the whole GROUP_CONCAT() result.Ĭan someone advise, whether the current DB structure is ok for the purpose and help me with the latter requirement? Or maybe there's a better approach to the problem? User can filter by values in any column, and only rows containing search phrase should be returned.įirst requirement I think is handled by the query I pasted above. It has to be sortable (by any column), meaning that user sorts asc by column 2, and rows are ordered properly.I am thinking about that design, because I have some doubts regarding performance and meeting requirements: Order by group_concat(dd.value order by field(`col`, 1, 2, 3) asc) Ībility to sort by any column is achieved by using group_concat(). Group_concat(dd.value order by field(`col`, 1, 2, 3) asc) as `values` Here's a query I am using to fetch rows: select `row`, Here's the sqlfiddle for my current structure. ![]() One of the requirements is to allow a user to create a "database" inside the application, that can have a variable number of user-defined columns (it's an excel-like structure). I have recently inherited a already started project, and I have one challenge right now. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |