Structure of a database for 'Secret Friend'

Question

I’m creating a secret friend system for the family, where it will be possible to have:

• Registration of users with their data (ex: Tennis size, shirt, etc...) ;
• Groups of registered users;
• Generate a draw with the users inserted in this particular group;
• Wish List;
• A mural in the group, to leave messages (Anonymous or not);
• Notifications (if there are any messages inserted in the group, friend requests, etc...);
• Relationship system (Friends).

What has already been done:

 TB_USUARIO |   TB_PERFIL   |   TB_GRUPO    |   TB_RELACIONAMENTO       |   TB_MENSAGEM_GRUPO   |   TB_DESEJO               
------------|---------------|---------------|---------------------------|---------------------- |-----------------
ID_USUARIO  |   ID_PERFIL   |   ID_GRUPO    |   ID_RELACIONAMENTO       |   ID_MENSAGEM_GRUPO   |   ID_DESEJO
ID_PERFIL   |   NOME        |   ID_USUARIO  |   ID_USUARIO_SOLICITOU    |   ID_GRUPO            |   ID_USUARIO
USUARIO     |   SOBRENOME   |               |   (usuário que solicitou) |   ID_USUARIO          |   DESEJO
SENHA       |   EMAIL       |               |   ID_USUARIO_RECEBEU      |   MENSAGEM            |
                                            |   (usuário que recebeu)   |   DATA_HORA           |

My biggest doubt is about Grupo de Usuários, Notificações and on Gerar um sorteio com os usuários inseridos em um determinado grupo. What is the best way to Structure the Database, and what is the logic(in php) that I can use to carry out the draw?

Answer 1

There are several modes and ways, depends on who is modeling the system.

Let’s assume the group table:

TB_GRUPO

• ID_GRUPO (identifier);
• NOME_GRUPO (name, hidden friend company Xyz);
• DATA_SORTEIO (which will be automated by the system);
• DATA_ENTREGA (would be the date of exchange of gifts);
• LOCAL_ENTREGA (would be the place of exchange of gifts);
• VALOR_MIN (would be the minimum present value, if it does not have min he gets like null for example)
• VALOR_MAX (would be the maximum present value, same idea of the minimum)
• STATUS (indicate whether it has already been drawn. the draw should happen automatically on the date indicated, or, if the admin wants before, by pressing a button for example, and this flag would indicate that it has already been drawn)

A group could have one or more administrators

TB_ADMS_GRUPO

• ID (auto increment, only to index)
• ID_GRUPO
• ID_USUARIO

A group could have several participants

TB_MEMBROS_GRUPO

• ID (auto increment, only to index)
• ID_GRUPO
• ID_USUARIO
• ID_AMIGO (would start as null, the draw would be filled with the id_usuario of the drawn friend)

At the time of the draw, you would select all members and fill the column friend of all. Take care in the draw algorithm. The ideal is to be cyclical, or be the first to give the gift always be the last to not break the delivery wheel. Do not draw one’s own person to oneself (basic).

For the notification, just send an email to the user’s email at the time of the action that must be notified. Don’t necessarily need a record for notification.

You can even register the notification, but in my opinion it is unnecessary, after all if you notify a user that they have received a new message, the message will be registered. If he receives an email informing this notification, it is enough for him to go into the system and check the message that IS REGISTERED. But the notification, if registered, what good would it do?

See examples, browse secret friend websites and see how they work, this will help you. A very good one to play is the http://www.amigosecreto.com.br.