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 ]
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.