CODEWITHSUNDEEP
phpmysql
David19801
David19801

Reputation: 11448

mysql lots of text columns or integer columns and a new table?

I'm new to mysql and have a choice regarding mysql database structure for my new database. I can have:

table1:(ID,text1,text2,text3,text4,up to text7)

OR

table1:(ID,int1,int2,int3,...up to int7) where the int values link to table 2 unique index

table2:(ID2,text)

So basically, should I put all text in columns in a single table or saparate and store index values in 1 table and the text data in a second table... queries will be given ID code, to return strings text 1 to text 7. Table will be large, about ~1 million ID's (meaning table 2 if method 2 is used would have ~7 million entries)

which method would be faster in your opinion?

EDIT1: Each text is 250 letter characters in length. EDIT2: To clarify, I am given the ID and the query to the table would be getting all 7 text items for that ID. This is the only query that will be done on the tables and the only information ever required. Just need to know which would be faster to use. If neither, please offer a better way!

Upvotes: 0

Views: 156

Answers (2)

profitphp
profitphp

Reputation: 8354

Won't make any speed difference if you're not querying based on the text. Keep it simple and just use 1 table. If you have to query by the text, then you're going to want to change it.

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

Please give more information and let us know what you're storing.

That said, however, storing more than one of the same "thing" in a single record in a relational database is usually the wrong approach. Both your proposed solutions involve storing 7 of something in a single record, so neither of them appears at first look to be a good solution.

The "correct" solution (absent any special conditions) would be to have one table with (in your example) 7 million entries.

Are you thinking of the second solution because someone suggested to you that you should "normalize" your text values by storing them in separate rows with an integer ID? If so, I don't think you've fully understood what they meant. The solution as specified does not normalize the database it simply adds one (unnecessary) level of indirection.

If you absolutely must use a denormalized solution, all the strings in one record is better than the added complexity of the integer IDs.

Upvotes: 1

Related Questions