Database structure for single user having multiple records.Please suggest

Question

User1804579801 posted

I have requirement of the below .i.e is one single user will have 5 or 3 crietria  records for 3 competitor so how would i maintain database structure for the same .Iam thinking of creation of table with userId and companynamea,competitor a,competitor  b, competitor  c as coloumns and will get 5 records for each user.Please suggest is it right way or not

criteria1	your company prodname	competitor a	competitor b	competitor c
criteria2	aaa	bbb	ccc	ddd
criteria3
criteria4
criteria5

Answer 1

User-821857111 posted

No, this is not the best way to design your structure. If you have a one to multiple (many) relationship, you need at least two tables - one of the principal in the relationship and one for the dependant.  In this case, I think you also need a table for you Criteria. There might be 5 at the moment, but customers change their minds and add things over time. 

Answer 2

User379720387 posted

It is not obvious from your post what the relationship is between user and company. Is this 1-1?

Also, I am thinking you will end up with a table criteriaType:

1 - criteria A

2 - criteria B

etc

Also, can one company have multiple product names?

And what is the relationship between criteria and competitors?