Let's start with the context. I'm writing a small application for myself, which can be used to alter and create products in a MySQL database.
Every product has at least one production method, for example 'forged', 'welded' or 'machined' (many more methods exist in my application). The production method for a product can be any combination of these methods.
The application currently has a dropdown menu with a list of checkboxes to choose the production methods from, and the production method is displayed by a textfield. When my application displays the production method, I want these to be ordered alphabetically (not ordered by clicked order, like if you first clicked welded that will be the first value):
product table: 'forged - welded - machined'
product chair: 'welded - forged - machined'
product chair: 'forged - welded - machined'
When my application retrieves the product table I also want to set the 'forged', 'welded' and 'machined' methods checked to true.
Should I use one field in the MySQL products table where a string of the combination of methods is saved (like above), and let my application handle the sorting when clicking the checkboxes and save this string to this one field in the products table,
or,
should I use a many-to-many relationship in my database where a table of all production methods exists, and I use a junction table to connect product (pID = 2) with forged (mID = 1), welded (= 2) and machined (= 3) like
pID - mID
2 - 1
2 - 2
2 - 3,
or,
should I use a table like
pID - method
2 - forged
2 - welded
2 - machined
What is performance-wise (lots of products and lots of methods) the smartest thing to do? Perform a bunch of query every time the application loads a different product (which happens a lot), or one query from the products table and load the methods this way (splitting the string and comparing each method with the checkboxes in a for loop)?
You should design the database as normalized. It it not necessarily the fastest option, but it is definitely easiest to manage. Most probably You want table of production methods anyway, so many-to-many relationship is a natural choice. It also makes tasks like filtering by production method a piece of cake. You really do not want to parse string on server to achieve this.
You can always precalculate those concatenated strings into table if You need faster response. Just do not use them as primary data.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments