0

I am developing a software in Java programing language and use MySQL as DB. This software collect some input from user, and then calculate/select the product he/she needs. First of all, he/she create a project(A table named project should be created) and calculate/select products,after that add them to the project. The product fields are different from another.( so we have different tables for each product named A, B, C ) Ex, I have 3 type of products, that each of them has specific fields and differ from another, thus the way user select/calculate each of them is different. Product A has 3 fields ( product_id, name, model ) Product B has 7 fields ( product_id, name, color, company name, .... ) Product C has 14 fields (product_id, name, company name, made by, created by, ..... ) I can not consider these products as one, cause the fields are different. Each products can be in several project, and each project can have several type of products.( Many to Many relationship ) Every project must save the products. In this situation, I should create many to many relationship between every product table and project? If yes How can I retrieve project again later? What is best practice for this situation? How can I consider these 3 products table as one?

alireza azadi
  • 77
  • 9
  • *"What is best practice for this situation?"* If you really need dynamic key values (EAV) storage some options are: 1) MySQL 8 [Document Store](https://www.mysql.com/products/enterprise/document_store.html) 2) MySQL "NoSQL" better said key-value storage using the [InnoDB memcached Plugin](https://dev.mysql.com/doc/refman/8.0/en/innodb-memcached.html) 3) MariaDB's [dynamic columns](https://mariadb.com/kb/en/library/dynamic-columns/) 4) Use XML (TEXT), JSON datatypes 5) Use NoSQL DBMS like mongodb – Raymond Nijland Aug 26 '19 at 17:28
  • @RaymondNijland: Thank you for your reply, I have seen some samples about orders and items.(Many to Many ) But in that samples, it seems that item fields are the same and there is only on existing of item type( Am I right? ) This is a small desktop software. And you are telling me If I want to continue using RDMBS, The only proper way is dynamic key values?Right? And any url for using XML, or JSON datatype? – alireza azadi Aug 26 '19 at 17:41
  • Well matter of tasts and opinions.. You also could go for [EAV (entity-attribute-value)](https://stackoverflow.com/questions/11779252/entity-attribute-value-table-design) storage in the database.. Question is when you store schemaless data why use a Relational database in the first place... Also a problem is when you need to convert records to columns so a application can use it more easy so you will need to pivot it.. – Raymond Nijland Aug 26 '19 at 17:59
  • @RaymondNijland This a crud software, And is based on the DB ( less calculate, more select! ) and our data is in excel files. One of the reasons I choose RDBM is easy importing of excel files into mysql.(With some tools exist in excel ) Another that is I was unfamiliar with this technique.( The EAV Devil ! ) I will try and let you know. – alireza azadi Aug 26 '19 at 19:31

1 Answers1

1

You might want to create a JOIN table to manage the inter-relationship between records in your other two tables.

For example:

  • project (project_id, project_name)
  • product (product_id, product_name)
  • project_products (pp_project_id, pp_product_id)

That way you can:

  • Create projects and products before you define their inter-relationships
  • Easily define the inter-relationship between projects and products without creating a complex schema

Usage example:

projects table:

project_id  |  project_name
------------+----------------
 1          | Country market
 2          | City carnival

products table:

product_id   |  product_name
-------------+---------------
 101         | Candy floss
 102         | Cheeseburger
 103         | Hamburger
 104         | Pizza - Hawaiian

project_products table:

pp_project_id  |  pp_product_id
---------------+----------------
1              | 101
1              | 103
1              | 104
2              | 101
2              | 102
2              | 104

In the example above...

  • Project 1 has 3 products against it
  • Project 2 has 3 products against it
  • Products 101 and 104 are both recorded against 2 projects
  • The remaining products are recorded against 1 project only
ChrisFNZ
  • 597
  • 1
  • 4
  • 21
  • I did not understand, could you explain more? You mean if I have 3 product tables ( one for each type ) I create three many-to-many joins with project table table? Then How can I retrieve data? – alireza azadi Aug 27 '19 at 05:34
  • My example demonstrated how to define a many to many relationship between two tables only, not three. If you have multiple product tables then you might need to agregate them into one (if practical) as that would simplify things otherwise you could create a view to present them as one table but ensure that each record has a unique primary id value. – ChrisFNZ Aug 27 '19 at 05:37
  • I've expanded the answer to demonstrate how my answer might look from a data perspective. – ChrisFNZ Aug 27 '19 at 05:53