什么是多数据存储和提取的最佳方法？

Question

The following table each owner has his own page, When someone visits it, All the Seats with SecretCodes appears

OwnersSeats
[id    -  ownderid -  type        -  Seat1  -  SecretCode2                            -> Seat50  - SecretCode50              ]
[1     -  3        -  Premium     -  Mark   -  v2ttvgwrxt5cg36g3c63g36c3c54c26y4e73c6 -> Max     - c4wghwh65qcg45g5qx3       ]
[2     -  3        -  Standard    -  Jerry  -  36vyh36cyh6y6wc363v636vc3yc6y3v7y37    -> Marco   - h7wv5cg6qg6qcgqx3xgf5qwy4h]
[3     -  7        -  Enterprise  -  Sonia  -  c3m73uhyv73vyhu3h33c65g33c7v373v73v7   -> John    - 5ctev5hmkbjev7h7kje       ]

With each Seat* VARCHAR(24), SecretCode* TEXT Can be VARCHAR(512) instead.

Then I select them using this Query

SELECT ownderid, type, Seat1, SecreCode2 -> 50 FROM seats WHERE ownerid = :ownerid

What is the best way to handle this to be Efficient, Well preformed and Easier to Use and Edit?

I thought about storing all the seats in array inside the table as a TEXT like this

$SS = [
s1 => 'Mark', sc1 => 'v2ttvgwrxt5cg36g3c63g36c3c54c26y4e73c6', 
s2 => '~~', sc2 => '~~~~~~~',
s3 => '~~', sc3 => '~~~~~~~',
s4 => '~~', sc4 => '~~~~~~~', -> 50
];

But i don't know if that Good or Bad for Performance.

I want to limit it to 50, So i have 100 column[SS] 50 Seats 50 SecretCode, Do I use normalization to handle this?

I thought it would be better to put all the SS in a Single row to be fetched all at once with all the ownerid data, Since i suppose it would be faster.

Also I don't know how can i fetch all the SS from a normalized table without using second query to fetch all the releated data from it or usingGROUP_CONCAT(), So I wonder which one is better for performance to be used.

[OSid - Seat  - SecretCode                            ]
[1    - Mark  - v2ttvgwrxt5cg36g3c63g36c3c54c26y4e73c6]
~48                                                   ]
[1    - Max   - c4wghwh65qcg45g5qx3                   ]
[2    - Jerry - 36vyh36cyh6y6wc363v636vc3yc6y3v7y37   ]
~48                                                   ]
[2    - Marco - h7wv5cg6qg6qcgqx3xgf5qwy4h            ]
[3    - Sonia - c3m73uhyv73vyhu3h33c65g33c7v373v73v7  ]
~48                                                   ]
[3    - John  - 5ctev5hmkbjev7h7kje                   ]

Answer 1

I'm using an answer, not a comment, because a comment is quite restricted. I think I start to understand your table. So I would rewrite the table with only these columns:

id
ownderId
type
seat  
secretCode

Data would look like this:

[id - ownderId - type       - seat   - secretCode                             ]
[1  - 3        - Premium    - Mark   - v2ttvgwrxt5cg36g3c63g36c3c54c26y4e73c6 ]
[2  - 3        - Standard   - Jerry  - 36vyh36cyh6y6wc363v636vc3yc6y3v7y37    ]
[3  - 7        - Enterprise - Sonia  - c3m73uhyv73vyhu3h33c65g33c7v373v73v7   ]
[4  - 3        - Premium    - Max    - c4wghwh65qcg45g5qx3                    ]
[5  - 3        - Standard   - Marco  - h7wv5cg6qg6qcgqx3xgf5qwy4h             ]
[6  - 7        - Enterprise - John   - 5ctev5hmkbjev7h7kje                    ]

If seat numbers are important to you, you could add a column with that number in it. Like this:

[id - ownderId - type       - seatNo - seat   - secretCode                             ]
[1  - 3        - Premium    - 1      - Mark   - v2ttvgwrxt5cg36g3c63g36c3c54c26y4e73c6 ]
[2  - 3        - Standard   - 1      - Jerry  - 36vyh36cyh6y6wc363v636vc3yc6y3v7y37    ]
[3  - 7        - Enterprise - 1      - Sonia  - c3m73uhyv73vyhu3h33c65g33c7v373v73v7   ]
[4  - 3        - Premium    - 50     - Max    - c4wghwh65qcg45g5qx3                    ]
[5  - 3        - Standard   - 50     - Marco  - h7wv5cg6qg6qcgqx3xgf5qwy4h             ]
[6  - 7        - Enterprise - 50     - John   - 5ctev5hmkbjev7h7kje                    ]

The table is now normalized. The same type of data is not present in multiple columns anymore. This would make your query look like this:

SELECT seatNo, seat, secreCode FROM seats WHERE ownerId = :ownerid

Any other query on the data would be similar.