List as a field type?

[TheFilmGod]

I was wondering if I could make a field type that has a list?

 

I wanted to put in # ids in it, whcih would link to other mysql tables. If not, I was thinking of making it a large varchar and then separate the ids using spaces.

 

Any help is greatly appreciated!

[freenity]

you can't make a list field

but

you can make a varchar field and separate those ids with spaces, of course you won't be able to access to those ids using sql but with a little help of php.

Check these functions yo'll need them:

 

implode()

explode()

 

[TheFilmGod]

you can't make a list field

but

you can make a varchar field and separate those ids with spaces, of course you won't be able to access to those ids using sql but with a little help of php.

Check these functions yo'll need them:

 

implode()

explode()

 

 

That is what I was thinking... but isn't there a better way? It doesn't sound very optimized...

[freenity]

I guess there is no other way =)

[aschk]

Actually there is:

You can use the "SET" datatype (NOT ENUM, they're different). This allows you to specify multiple options in your field. However if you're planning on using this method please consider using another table and normalising your data instead.

 

If you could give us an example of the data you're planning on storing and the database layout you think you might use, then please post it here and we'll offer our advice on how to make it better.

[fenway]

You probably don't want a list... nad you definitely don't want SET.

[aschk]

oooh, what's wrong with SET (apart from non-normalised data)? I'm beginning to think perhaps I missed some quirk in the MySQL docs...

[fenway]

From the refman directly... "Why You Shouldn't Use SET" ;-)

[luca200]

From the refman directly... "Why You Shouldn't Use SET" ;-)

Can't use commas and no more than 64.... I don't think these are tragical limits 

[fenway]

True enough, but it's too cumbersome to update (add/edit/delete) them for it to be very useful.

[TheFilmGod]

Maybe my question was too specific - maybe this will help...

 

What I want to do:

 

I'm creating a comment system on my website. I would like to create 2 mysql tables.

 

Table Comment:

 

Id | Time | Author | Comment

 

Table Pages

 

Id | # of Comments | Comment ID's

 

 

When the php needs to grab the comments for a specific page, it would go into the table Pages find the row, extract all comments by there ids, and then go into table Comment and load the actual comment text.

 

My question was concerning creating the field type "Comment Id's" in table Pages. What field type would I use?

 

... Maybe my logic behind this comment system is flawed. Maybe a new table should be created for each new page on the site - but this would create potentially thousands or even millions of database tables... Is that better, or worse from my original schema? Thanks!

[freenity]

in the comment table you should make a field called pageid for example where you put the id of the page where those comment belong to.

So to select all comment from page id = 4 do: select * from comment where pageid = 4 > so you will have several results..

 

Thats the best method I think

 

[aschk]

Assuming that you can't have the same (1) comment on several different pages ( many -> to -> many relationship), then you should really be storing the page_id in the comments table.

NOT the comment ids in the pages table (assuming a page is unique), otherwise you're creating an unnormalised structure.

 

And i'm guessing that your pages table has more than the columns you specified above (i.e. more than Id | #of comments | comment ids).

A page might be: id | title | description

Each page is unique (therefore is probably the PRIMARY KEY)

 

YES you could denormalise some of the information so that you have an easy lookup for the number of comments for a page, but to be honest i don't think you'll need to be doing that, and a simple COUNT(*) on the comments where page_id = <number here> will be sufficiently quick.

 

note: denormalised and unnormalised are different things; google is your friend

[fenway]

You can always have a summary table to keep count.

[TheFilmGod]

Good ideas. I do understand what denormalized and unnormalized mean. I am very well educated in that field.

 

I was thinking that made it would be even better to simply create a new table for each page? Of course, I would only do this for pages that would receive heavy commenting. Other pages - wouldn't have that.

 

Another question, how does youtube/facebook put the comments in their database? Any ideas? This probably would be the best model.

[aschk]

...I do understand what denormalized and unnormalized mean...

 

In which case you should know that a new table for each page wouldn't be normalised.

 

Your normalised table structure should be something like:

 

pages:
Id | title | #of comments
===================
1  | page1 | 4
2  | page2 | 11
3  | page3 | 2

comments:
Id | page_id | comment
====================
1  | 1       | love the post
2  | 1       | yeah great post thanks man
3  | 1       | wicked sick
4  | 1       | pwned
5  | 2       | could have been better
6  | 2       | not the best post i've seen
... 9 more comments for page 2
16 | 3       | i'm not sure facebook is the answer
17 | 3       | you could always think about it logically spock!

 

The #of comments in the pages table is a denormalised field, and the comments store the page id, NOT the page storing the comments ids...

 

I think the problem here is that you are talking about "pages" when you don't actually mean pages.

Facebook has a comments system in the "Wall" application. Each wall is applied to a user, each wall has many comments (from other users). So really when talking about pages you're talking about users (seeing as a user->wall relationship is 1-to-1).

 

note: i don't know for 100% certainty that facebook use this model. They may or may not, my "guess" is merely that and does not represent the actual layout that facebook employ.