Database design question with message boxes

Question

I have a question and I'm not sure about the best way to design it.

Basically, there are users. Each user can have a message box, and each box in turn can have several messages.

The problem is, is that there are some special message boxes, including "New," and "Trash." A user is required to have these two boxes.

I had some ideas about what could be done. One of my ideas was for each message box to have a field recording its type (enumeration of "new," "trash," or "other). But that doesn't force them to have those boxes, which I would prefer.

I could also have each user have fields specifically relating to their special boxes (so in the user table there would be fields like newBox and trashBox). Of course, if these are required, then the user and user_messageBox tables would be mutually related, which would cause obvious problems.

Then, I could make it many-to-many where the message box table doesn't relate to users, so there would be another table for relating these two as a many-to-many. But I don't want a many-to-many relationship, so it doesn't solve anything!

Answer 1

If you want just one message box per user, then call "new" and "trash" something else, like a state. Have this be a field of the message. (You'd also need a field for the user or equivalently the message box in each message as well.)

This gets you thinking outside of the box (!) and may give you other ideas as to how to use that field if you so choose: "Flagged," "important," "spam," etc.

Answer 2

Why dont u use a message_box_type field,.

I think you should force the users to have both the message box types using application logic.

Ideally there would be two tables, a messages table with a foreign key id message_box_id that links it to another table message_box. The message_box table in turn has a message_box_type field in addition to other fields. The application logic would make sure that two message_box records of the two types 'new' and 'trash' are created on user signup/creation