I have 2 fields in my order table. Product name and product level.
Example lift A, lift A will go level 1 , 2, 3. Lift B will go level 4, 5, 6
I wanted to use delimeter to store the value in product name and product level.
How to store them in such a way it is easier to insert the value in and retrieve the value and display it in a table format.
分享 The short answer: No you don't want to do this. Never store more than one value in a single column.
Instead, you should store six different rows in the database:
lift level A 1 A 2 A 3 B 4 B 5 B 6
This way it is easy to store and retrieve rows.
The long answer: Database engines are designed to be able to look up and provide information quickly. One of the key factors in this is indexing.
Think of a database index like a telephone book. If you want to find a name "Flintstone" for example, first you would skip partway into the "F" section, then look for "Fl", followed by "Fli", etc. A database index does the same.
Now, following the same example, what if you wanted to find everybody's name who contained the characters "lint". You could not use the index because while there may be a name "Flintstone", there most likely is a "Clinton", and possible a "Splinter". Your only recourse would be to look through every name trying to find your string.
This latter example is exactly what you'd end up by storing a value of "1,2,3" (or "1;2;3" or even "123") in a single column. If you ever wanted to know which lift is matched to level 2, you could not do this without first extracting the value "1,2,3", then looking inside for a match of 2.
This only becomes more difficult when joins come into play and you had more information for each level. For example if you have a table "level_names" which stored a name for each level. If you had the "1,2,3" schema, what would you want for a result row when joined to this table? "A", "1,2,3", "First,Second,Third"? Now what if a name contained a comma? You would be setting yourself up for a parsing nightmare and doing a lot more heavy lifting in the application than should be required (database engines, whether they can or can't natively do this, are really not designed for this kind of string/output manipulation)
分享
