How to store multiple sets of values in a database?

Question

Right now I'm creating a web-app which requires that multiple sets of data (including a URL, title, and date) be stored in a DB.

So for example, User1 may be storing Item 1 which consists of Example, http://example.com, and YY-MM-DD HH:MM:SS. And he may have multiple items with the same types of data.

Before, I was sotring all the items in one field of my users table with something like:

Example=>http://example.com=>YY-MM-DD HH:MM:SS[END]
Example2=>http://example2.com=>YY-MM-DD HH:MM:SS[END]
etc...

That would be combined to one big string, and then in PHP I'd separate each string by the separator [END] and then separate each of THOSE strings by the separator =>. Now I'm wondering if it would be better to store these items in their own table, so each user would have an 'Items' table (user1_items, user2_items, etc.). Is there a 'right' way to do this? Would using separate tables be overkill? Also, about how much more space, if any, would I be losing on my web-host? Just looking for some guidance on how to procede with this.

If any further elaboration is needed, please let me know!

Answer 1

Have a user table with information about each user along with a unique user id.

Have a data table where each piece of information (url, title, date) is a separate column. Add an additional column called user_id that indicates which user this piece of information belongs to.

Answer 2

I would follow this form, because it turns out to be to me more rapid.

With every unique user's index, it would create a consultation towards the table of articles connecting this unique user's index with a unique sub-index that would have the table with the information Example => http: // example.com => YY-MM-DD HH:MM:SS [END] Example2 => http: // example2.com => YY-MM-DD HH:MM:SS [END] etc...

For example:

---

The user's only index, p.e. userid = '98'

[User table]

    (Userid (Index))
    (Name)
    .
    .
    .

And


[Articles table]

 Subid(Articleid) 98
 (Url) http: // example.com
 (Datetime) YY-MM-DD HH:MM:SS

--------------------------

 Subid(Articleid) 99
 (Url) http: // example.com
 (Datetime) YY-MM-DD HH:MM:SS

__________________________

The same user's index connected to the same sub-index of articles. You were saving yourself consultations in the database, because with a consult you'll have all information.

Answer 3

I agree to go for separate tables.

Also note that if you do decide to insert multiple values into a column, you don't need to insert your own separators like [END], just store it in an array and use serialize($array) to serialise it before storage, then $array = unserialize($dbResponse); when retrieving it from the database. This is assuming that the data is sanitized and safe.

Answer 4

Putting your data in one long string would defeat the purpose of a database. It would be the equivalent of writing it to a text file. Databases are a way to organize, index, and relate data to each other.

To visualize a database structure imagine a table like so.

   id      name           url                         date
  ----    ------------   -----------------------     --------------------
    1      Example1       www.example1.com            10-07-14 13:56:13
    2      Example2       www.example2.com            10-07-15 00:06:37
    3      Example3       www.example3.com            10-07-16 16:20:00
    4      Example4       www.example4.com            10-07-17 09:18:22

each one is a row in the database and you can pull out a bunch of them based on a query and then do whatever you need with them.

Also you could relate this data to the user who entered that data by adding a user_id column and inserting their user id in each record which would reference their user record in the users table.