How to design a database for query builder project

database-design

I have this project in which user can create queries using web based UI in which there will be list of columns and applicable operators. Later users can edit these queries, too. So I need to store this in database.

I can store query in table as simple string but then editing will be not possible so I need to store it some other way. So I somehow managed to design it following way.

So let's say I have to store this query:

C1 > 8 AND (C2 <= 7 OR (C4 LIKE '%all%' AND (C1 > 15 OR C2 <= 3)))

where: C denotes some column

If I have to store it in DB as shown in image,

I would group each condition and store it in sub_operand table
then there will be recursive mapping entry in op_master table for each entry in sub_operand table
finally there will be master entry in op_master

But it seems too much complicated to handle insert and update. Can someone help me with this? I am very much stuck here.

UPDATE: I think I am missing something here in schema. It won't work as I have thought. I will update question as soon as I can correct it.

Best Answer

One approach you could take is to design a table that implements a stack into which can be placed the components of the logical expression in reverse-polish (postfix) notation. In such a design, the components of the predicate are each inserted into the table with an order number to specify the reverse evaluation order. To evaluate them a query is written to read them out by order number, apply the operators when encounter, and store the intermediate result to be applied the next. The use of the reverse-polish notation eliminates the need for parenthesis and can support nesting of expressions to any level.

Here is an article specific to SQL Server that shows such a solution. Now in this article the author is developing a more complex example of implementing business rules to determine whether an employee is authorized to execute a particular operation. It can easily be adapted to your use case as in both cases what is being implemented is a logical expression storage and evaluation engine.

I hope this can point you in a direction that will enable you to more easily handle the complexity that parsing nested logical expressions bring!

Related Solutions

Mysql – Database design for mobile app

How to download only the newer and updated entries, and avoid downloading same entry once again?

Use a database synchronization tool like SymmetricDS or Daffodil Replicator.

You'd have a central database server, like PostgreSQL, with SymmetricDS running on it.
Each client, such as an Android smartphone, runs SQLite and SymmetricDS.
The SymmetricDS clients and server communicate with each other over HTTP(S), sending changesets back and forth.

How to provide edit entry possibility to db? Is it better to create new table with proposal entries and after verification, move them to main table? Or maybe should I create additional columns, which would be proposals for new values?

I would add an is_approved or similar column that needs to be true for items to show up to regular users. Editor and admins can see unapproved items.

What if I would get lots of changes proposals? Is it better to store only the last one? Or maybe block edit possibility if a proposal is already set?

Use History Tables (copy-on-write to a table similar to your main tables) to capture each change. That way you can rollback to earlier or better versions if you need to. Use Optimistic Concurrency Control to prevent updates if data changed.

How to avoid cheaters and scammers? Some bored people may want to try make a mess, editing multiple entries with fake data. How to avoid such kind of events? Grant edit access only to verified people?

Like this site, add "user abilities" after users have proven themselves. Block bad users. Use machine learning to filter spam.

If I would like to store images of items, is it better to store it in db or use some kind of flicker or instagram instead?

Usually database is better, if you can cache heavily on web server, and can do incremental backup.

Is it better to save new entry in SQLite instant after edit? Or just post it to MySQL and add it to SQLite only after verification and only via SQLite and MySQL sync?

Saving to SQLite locally first allows for offline usage, and is probably more responsive. http://offlinefirst.org/

Is it good idea to set key value to EAN code? At least it is constant and connected to only one product. There will be no items containing no EAN.

No, I would use a meaningless key for product_id and a unique nullable column for EAN.

Optimum design / structure for product data database

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table.

This means that each product can have as many attributes as it needs.

Table structure would look something like this.

Products
Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

Attriutes
Attribute_Id INT PK Auto Increment
Product_Id INT FK
AttributeName VARCHAR(255)

AttributeValues
AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255)

Best Answer

Related Solutions

Mysql – Database design for mobile app

Optimum design / structure for product data database

Related Question