4 Replies - 768 Views - Last Post: 04 December 2010 - 10:22 PM Rate Topic: -----

#1 Ntwiles   User is offline

  • D.I.C Addict

Reputation: 148
  • View blog
  • Posts: 831
  • Joined: 26-May 10

Storing a List

Posted 04 December 2010 - 04:37 PM

Hey guys. I'm trying trying to store a list of blogs a user is following in a row title 'following'. I wanted to make sure I was going about this right.

Is the best way to do this to just use a comma-separated list of the blog ids? And are there any built in functions that can be used to make this more efficient? Thanks (:
Is This A Good Question/Topic? 0
  • +

Replies To: Storing a List

#2 macosxnerd101   User is online

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12316
  • View blog
  • Posts: 45,416
  • Joined: 27-December 08

Re: Storing a List

Posted 04 December 2010 - 06:38 PM

It would probably be a better idea to design a table for blogs_followed. The PK should identify each row, and an FK should be user_id for the user that is following the specific blog. I would also have blog_id as an FK to identify the blog. Then when you query, select all from the table where the user_id is matching. This will give you a list of blogs the user is following. This also allows you to join on the blog table and gather more information.

In general, not good practice to store multiple values in a single field. Try to make it as easy as possible on yourself and others to update and query the database.

The basic table design:
following_id (PK) | user_id (FK) | blog_id (FK)


Was This Post Helpful? 1
  • +
  • -

#3 Ntwiles   User is offline

  • D.I.C Addict

Reputation: 148
  • View blog
  • Posts: 831
  • Joined: 26-May 10

Re: Storing a List

Posted 04 December 2010 - 09:10 PM

Thanks for the reply. That definitely seems easier. That's not a waste of resources though? I'm almost sure I'll never need to make user of a unique id for each 'follow'. And as the number of members grows wouldn't that be a lot of unnecessary filtering to find the right ids, or is that not really an issue?
Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101   User is online

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12316
  • View blog
  • Posts: 45,416
  • Joined: 27-December 08

Re: Storing a List

Posted 04 December 2010 - 09:31 PM

That's not so much of an issue in the database world. If I was writing this in Java or some procedural programming language, I'd design classes and use data structures like ArrayLists. In the database world, this isn't great practice.

As for the filtering, you can work to optimize your queries depending on what you are filtering for. However, in SQL, you tell it what you want and let it worry about how it specifically gets it (ie., in terms of things like loops).
Was This Post Helpful? 0
  • +
  • -

#5 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Storing a List

Posted 04 December 2010 - 10:22 PM

You may want to check out:
Relational Database Design - Normalization (Look for the 1NF, specifically.)


View Postmacosxnerd101, on 05 December 2010 - 01:38 AM, said:

The basic table design:
following_id (PK) | user_id (FK) | blog_id (FK)


You don't really need the following_id. Just make the user_id and blog_id a composite PK. - Also prevents you from pairing the same values twice.
user_id (PK,FK) | blog_id (PK,FK)


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1