0

Let's say I have a Text field called "description" inside "products" table.

Inside this field I will store 3 values. I will separate them with two pipes ( || ):

requirements:
- bla bla.. (a text)
||
info:
- bla bla.. (a text)
||
specifications:
- bla bla.. (a text)

I am doing this because it looks better than having 3 different Text fields to do the job (which will increase the table's size).

Is this a bad idea? Should I use 3 different fields? Does 3 text fields really affect database performance?

3

Is this a bad idea?

Almost certainly. If code only ever treats that column as single un-parsed continuous string then it may be OK1. As soon as you want to pull one of the three values it is definitely a bad idea. Even if the parsing happens in the application it is additional complexity that need not be there.

Should I use 3 different fields?

Yes.

Does 3 text fields really affect database performance?

Not in any way you will ever notice. Depending on your particular DB software those "text" columns may be treated just like any other data type. Also you must consider how optimized your code to handle this will be compared to that written by a room full of PhD's who built the DBMS and do just this for a living.



1 Even then I'd say have separate columns.

| improve this answer | |
1

I agree with Michael Green and have some additional points to add.

Is this a bad idea?

Yes. It might seem like it's the more convenient option for you right now, but a bit later you may come to regret this. Anyone else who has to query or report on this will use "many swear words" when they find it. The time you save now will be lost everytime you have to parse unpack and check these in the future.

Should I use 3 different fields?

Yes, or alternatively consider an XML (or JSON) field. I don't like XML, but at least it has some concept of structure, and can be queried with some confidence. It will also be easier to extend when you realise you need 4 parameters.. or don't want the 2nd any longer.

A clear advantage of using separate table fields is that you now rely upon data typing and can now relying upon the database to enforce rules such as whether a value can be NULL or not.

Does 3 text fields really affect database performance?

Not significantly, especially as it's the same amount of text so its taking up the same amount if space, or even less if you consider the delimiters. By using separate fields with indexes query performance is very likely to improve.

Relational databases have a lot of power and capability, which you will not be able to exploit if you cludge it all together. What might seem like a shortcut now will come back to haunt you later.

| improve this answer | |
  • I also think that 3 columns would better, However, nowadays I would prefer JSON over XML for structured content as most modern DBMS have better support for JSON processing than for XML – a_horse_with_no_name Jun 18 '18 at 7:34
  • @a_horse_with_no_name I must confess I have no hands on experience with JSON, but judging from what I've just read in this case I think they'd serve the same purpose.. So I'll add it to the answer. – Sir Swears-a-lot Jun 20 '18 at 3:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.