0

In my system I have two users:

  • Request sender
  • Users receive the requests

I want to create my database tables 

1-requestTable 
__________
requestid 
createdat 

2-usersTable
__________
userid
name
answer(yes/no)

If I have 100 user and I send only three request only  will be like this 

RequestID  || UserID || Answer  ||
________   || _____  || ______  ||
req1       || user1  ||  yes    ||
req1       || user2  ||  No     ||
req1       || user3  ||  No     ||
req1       || user4  ||  Yes    ||
req1       || user5  ||  Yes    ||

this will insert 300 record for 100 user for 3 request only and this is not optimal.

What is the optimal way to create the database in the optimal way?

Brian Tompsett - 汤莱恩
  • 5,195
  • 62
  • 50
  • 120
mostashaar
  • 83
  • 1
  • 9
  • Why do you say 'this is not optimal'? You are concerned about the number of entries/rows? Think twice! You have to store that information anyway, right? And storing it in sequential rows like you suggest has two huge advantages: first it keeps to code simple and 2 it allows easy and efficient selection, filtering and retrieval of the values on database level. This is what databases are optimized for. The only alternative would be to pack all user results into an associative array and store that in serialized form. Works, but you give away all the power of your database! – arkascha Nov 18 '12 at 15:42
  • Actually, this IS the most optimal way to do it. All you need now is sane indexing and you have the most efficient system. Database efficiency most of the time isn't about the number of rows - it's about how you traverse through them! – Sébastien Renauld Nov 18 '12 at 15:43
  • Thank you for the answers, but how to do the relations between to tables, like 1 to many relation. @arkascha – mostashaar Nov 18 '12 at 16:55
  • What is usually done is this: you have _tree_ tables: one holding the requests (with all their attributes as columns), one holding the users (with all their attributes as columns) and a reference table modeling to relationship between requests and users. That third table is really crunchy, it only saves numerical index values from other tables, so writing and reading is really cheap (fast). As correctly pointed out by @user1161318 below it is enough to store either positive _or_ negative relations, the absence of a relation marks the opposite. – arkascha Nov 18 '12 at 21:13

1 Answers1

0

If requests are sent to everyone, then you can just store the yes answers, which will reduce the number of rows.

RonaldBarzell
  • 3,752
  • 1
  • 14
  • 23