Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2016
    Posts
    2

    Efficient design for a products database

    Hello,

    First-time visitor here seeking guidance/wisdom for a project idea.

    I'd like to create a web database where users can search for niche technical products (quite complex in nature). The search categories are industry, domains, sub-domains, parts/sub-systems and application cases. Each product also has specific characteristics such as solution method, price, integrated and required modules. In terms of workflow, I envision users selecting 2-3 primary categories and adding product feature filters (e.g., method, price, etc.) to produce an output listing of matching products. All this should be easy enough, except....

    Each industry has multiple domains, each domain has multiple sub-domains each with multiple sub-systems and so on. Multiple products can match multiple combinations so there are several many-to-many relationships. Of course not all industries will have the same domains, so I can filter out ones that are irrelevant. Even after this, the brute force method of one line per will obviously lead to quite a large database with numerous duplicated product entries. For example, 15 industries * 10 domains * 25 sub-domains * 40 sub-systems, etc, will equal thousands of entries - this will no doubt have an impact on performance, not to mention maintenance/update tasks.

    Is there an efficient way to design a schema for this? It's like an Ebay or Amazon but on a much smaller scale. Is a database approach the right way or is there a different method prescribed for an application with several many-to-many relationships and duplicate products?

    Sorry, I'm an engineer with very little exposure to databases. Trying to learn, so I appreciate your patience. Thanks in advance for any guidance.

    Cheers,

    Joe

  2. #2
    Join Date
    May 2016
    Posts
    89
    Hi Joe,

    Your functional specifications are incomplete. You have to precise cardinalities in the both ways.

    For example, between domain & subdomain, there is a many to many cardinalities from domain to subDomain. It means for one domain, there are many subdomain, but from subDomain to domain what are the cardinalities?

    Just for illustrating this, let's take the example between flats and owners?

    An owner can own between 0 to N flats
    But a set of owner can also buy the same flat so the cardinalities from a flat to a owner is many to many too. It means a flat can be owned by 1 to N owners

    But let's imagine that the law prohibits multi-owners, so cardinalities are One to One. It means a flat can be owned by 1 and only 1 owner

    What are the consequences on the database model:

    First example: Many to Many for the both ways => 3 tables
    Owner, flat and WhoOwnes with only two fields the Owner & flat id

    Second example Many to many & 1 to 1 => 2 tables
    Owner, Flat with the owner id.


    Speical case: Second case but you have to store historic of owners => first example i.e. many to many & many to many with a third field dateOwner

    To be more explicit, find as below the conceptual data model
    Click image for larger version. 

Name:	product_cdm.png 
Views:	6 
Size:	33.5 KB 
ID:	17049

    But are the cardinalities 1 to 1 right?
    Last edited by informer; 07-14-16 at 16:13.

  3. #3
    Join Date
    Jul 2016
    Posts
    2
    Thanks for the note. A sub-domain can only belong to 1 domain, so the cardinality is 1:1. To extend this further, subdomain-to-parts and vice-versa are many-to-many. Subdomain-to-application is 1:many and application-to-subdomain is 1:1. Hope this helps clarify.

    Thanks again,

    Joe

    PS: The captcha on this page is awfully difficult to decipher. I've refreshed a dozen times and still can't get it right.
    Quote Originally Posted by informer View Post
    Hi Joe,

    Your functional specifications are incomplete. You have to precise cardinalities in the both ways.

    For example, between domain & subdomain, there is a many to many cardinalities from domain to subDomain. It means for one domain, there are many subdomain, but from subDomain to domain what are the cardinalities?

    Just for illustrating this, let's take the example between flats and owners?

    An owner can own between 0 to N flats
    But a set of owner can also buy the same flat so the cardinalities from a flat to a owner is many to many too. It means a flat can be owned by 1 to N owners

    But let's imagine that the law prohibits multi-owners, so cardinalities are One to One. It means a flat can be owned by 1 and only 1 owner

    What are the consequences on the database model:

    First example: Many to Many for the both ways => 3 tables
    Owner, flat and WhoOwnes with only two fields the Owner & flat id

    Second example Many to many & 1 to 1 => 2 tables
    Owner, Flat with the owner id.


    Speical case: Second case but you have to store historic of owners => first example i.e. many to many & many to many with a third field dateOwner

    To be more explicit, find as below the conceptual data model
    Click image for larger version. 

Name:	product_cdm.png 
Views:	6 
Size:	33.5 KB 
ID:	17049

    But are the cardinalities 1 to 1 right?

  4. #4
    Join Date
    May 2016
    Posts
    89
    Hi Joe,

    I changed CDM according to new information

    Click image for larger version. 

Name:	product_cdm2.jpg 
Views:	4 
Size:	113.9 KB 
ID:	17050

    But there are still some issues.

    Is there relation between
    1. Part entity and other entities
    2. Application entity & other entities



    Does subSystem entity exist?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •