I would like to store user information in a table.
First example The fields are eg. "has car", "has flat", "has cat", "has dog", "has LCD TV", "has notebook"...
The value of fields can be y/n or y/null (null means empty value in table).
The fields above are filled random, eg. "y", "null", "null", "null", "y", "null" OR "y", "n", "n", "n", "y", "n"
So only those fields contain useful information for me which is filled in "y", because when I use query I just want to see the result of those fields, which contain only "y" value.
Is there any possibility for this because obviously each records' result will differ from the others?
The second solution I create a table which stores the user id and the property of the user, eg.
user id property 1 has car 1 has dog 2 has LCD TV 3 has flat 3 has notebook 3 has cat
Here the unnecessary information are not stored but DB can have more than 100000 rows.
My question is what is the best store method. About 100-120 property will be used to one user. And there will be another table which will connect to the original table which also will contain further 50 similar property (y/null).
I think the second solution is best but I have doubt with speed. Of course primary key will be used and the more important fields (foreign key) will be received "Unique". (I heard with unique we can received the results more quickly.)
What do you think of writing above?
Thanks for your answers in advance.
Best Regards&Nice Day, Atti
From my point of view, if I understand correctly, best solution here is to create many to many relationship between table user (which contain user_id and some info about user if it's required) and second table let's call it has which will contain has_id and name(there will be stored what user have dog, tv, cat, etc.).
To create many to many relationship you need third table, called a junction table, which can be named user_had and which contains pair of field user_id and has_id which will be primary key pair (that means that you won't be allowed to have two identical pair (user_id and has_id) in the table).
So when you want to pull out data from table let's say what user under user_id 5 have you will do that with something like this
SELECT user_has.user_id, user_has.has_id, has.name
FROM user_has INNER JOIN has
ON user_has.has_id = has.has_id
WHERE user_has.user_id = 5;
when you inserting you have to insert only user_id and has_id into user_has table which will make new pair... etc..