CODEWITHSUNDEEP
database-design
dewyze
dewyze

Reputation: 979

Storing Usermade list in database

I'm making a site that allows people to create an event, people associated with it, and a to-do list. For the people, there would be names/phone/title. For a to-do list there would be a task and a boolean for whether its completed.

My question is do I just enter an array into a single field in a table (Events) in the database to represent the people or do I create a separate table for "associated people" and link them with a JOIN. My concern is that all those people will be user generated and it seems like a waste to create a whole other table. Likewise, I could create a "to-do" table with ID, task name, userid, and a boolean, but each of these lists would most likely never relate to anything else in the database other than the user who created them and the list they belong to. I could create a To_Do_Lists table, and then table items and do likewise with the people, but I'm not sure if that is really the best way if those values have no place outside that specific event belonging to that specific user.

Would it best to have more tables, store data as arrays, store data as xml as a blob, or what?

Upvotes: 0

Views: 61

Answers (2)

user29053
user29053

Reputation:

If you will be using a database for this application, following the best practices really will save you time in the future.

Here is how I would probably design the database:

People Table PeopleID, Name, etc.

Event Table EventID, Event Name, Desc, etc.

ToDoList Table TodoListID, EventID, OrderKey, Task, etc. This table is a pivot table. EventID is a foreign key to the Event Table. To find all the tasks for an Event, you have to JOIN with these keys.

Attendee Table AttendeeID, PeopleID, EventID, etc. This is also a pivot table. Joining with EventID will find you all attendees. Joining with PeopleID will find you all the events a Person is attending.

Upvotes: 1

Joel Brown
Joel Brown

Reputation: 14398

Store things so that each tangible object is in its own table. This will result in a relational database. You want to strive for third normal form. There are lots of reasons to follow this well established convention. Database and programming tools are built to work with data in this form and you will save yourself time and trouble and leave yourself a lot of flexibility for future changes and expansion.

For example, if you keep people in a PEOPLE table you will find it very easy for events to start overlapping people. Your system thereby becomes social without having to scrap your database and start again.

Resist the temptation to cut corners. It won't really save you anything in either the short or the long run.

Upvotes: 1

Related Questions