python72 Posted February 26, 2011 Share Posted February 26, 2011 Is there a way to be able to vary number of fields in database? Just to explain I will use an example. Lets say we have table with user details, the table has the following fields: User Name - single value for each user User DOB - single value for each user Kid's Names - this can vary, someone can have one kid, others can have several So I am basically looking for a way to accomodate the varying number of kids names for different users, something like array in programming. What is the proper way to deal with this in mysql? I am new to database and web programming in general so all I can think of right now is to create more than enough fields, like Kid1, Kid2, Kid3 ... Kid20 and when processing read them till null or empty field appear, but I think there should be better way to do it? Thanks for all your help. Quote Link to comment Share on other sites More sharing options...
requinix Posted February 26, 2011 Share Posted February 26, 2011 The proper way is to learn about this concept called database normalization. Core concept #1: if you find yourself wanting to create columns like X1, X2, X3... then you need a separate table. The user table has an ID number and the simple user stuff (name, birthdate, etc). Then you create a table for the kids: an ID number, user ID, and kid (whatever that involves). If a user has one kid then they'll have one row in the kids table. If they have two kids, two rows. And so on. Quote Link to comment Share on other sites More sharing options...
python72 Posted February 27, 2011 Author Share Posted February 27, 2011 Thanks for the answer, I thought about that but hoped there is an easier way. It seems that if there is few fields that can have variable number of entries the queries will be very resource and time consuming, am I right or not? Quote Link to comment Share on other sites More sharing options...
requinix Posted February 27, 2011 Share Posted February 27, 2011 Thanks for the answer, I thought about that but hoped there is an easier way. It seems that if there is few fields that can have variable number of entries the queries will be very resource and time consuming, am I right or not? It is the easier way. At least it will be when you try to write logic for the whole thing: using multiple columns is hell to program for. So I guess the answer is "no". It will not be resource or time consuming. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 27, 2011 Share Posted February 27, 2011 Yeah, don't cheat. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.