• 0

Database Design Help


Question

Hi i am trying to design a database and am kinda stuck. So as of now when a new product is added a trigger updates the products_have_criteria table by iterating through all the criterias, this will work when adding new criteria but not if you only want a subset of the criteria.

post-266102-12697910343043.jpg

Ex.

Criteria (length, width, weight, height) but for a new product you only need l,w,h.

So what would be the best way of trying to solve this. Two ideas that i have are

1. create a criteria_group table, so every time you use a new subset you create a new group. and add groupID to products_have_criteria table and will have to use the latest groupid to get the cid.

2. create a do_not_include table, and include cid that you want to skip over. In products_have_criteria table will be if (cid exists in do_not_include table) count++ else add to list.

I think do_not_include table may be better as it has less redundancy as say you want to add back a removed criteria you would just remove the cid from the table whereas with group way you first check if that set already exists if not then add it in, it just seems longer. What do you guys think? is there an easier or more efficient way.

Thanks

Update: After taking some time i realized i was going about it the wrong way rather than create another table and relationship, all i did was create a bool var in criteria called notUsed and just need to check the notUsed value in my trigger.

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

ok, the first line.. good database design should not use triggers for business logic.

you should build this requirement into any stored procedures used to read/write to these tables instead..

as for your question, just set the field to allow null entrys? unless i am missing something :)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.