0

I'm a beginner and I want to know what is the best way for the architecture of a bdd, I give you an example:

I have a user table:

  CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `pseudo` varchar(50) NOT NULL)

In my process I have some survey who have a state waiting,work,ended and I need to store the ID of the survey too.

So the first possibility is to create a table survey who have this value :

CREATE TABLE `user_survey` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `state` smallint(3) NOT NULL)

But in my backend I need to question 2 time if I want take the user, and search for his surveys.

The second possibility is to add a row in user table called survey and store the data like this : surveyId,stateSurvey;surveyId,stateSurvey etc. And iterate this data in my backend to search all survey I need.

What is the best practice/way to get this kind of data. And there is other way more "simple"?

halfer
  • 19,824
  • 17
  • 99
  • 186
Louis Brahmi
  • 834
  • 2
  • 9
  • 21
  • 1
    The most common is approach two because it reduces typing errors (plain wrong typing errors and or case sensitive) and diskspace vs approach one.. And use a JOIN with user table when searching on a survey state or name. – Raymond Nijland Oct 29 '18 at 22:36
  • @RaymondNijland thank to your reply, so I will make like the second approach ! – Louis Brahmi Oct 29 '18 at 22:40
  • "I think Raymond has been smoking something" No @Strawberry but iam pretty tired for some hours now – Raymond Nijland Oct 29 '18 at 22:45
  • "reduces typing errors" more possibilities of errors in the second possibilities no ? – Louis Brahmi Oct 29 '18 at 22:46
  • ""reduces typing errors" more possibilities of error in the second possibilities no ?" i mean with data integrity the normalized method is less error prone @El-Burritos – Raymond Nijland Oct 29 '18 at 22:49
  • @RaymondNijland Ok thanks ! – Louis Brahmi Oct 29 '18 at 22:50
  • 1
    You might like to read [my answer to Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Jun 06 '19 at 22:32

0 Answers0