TalkPHP - View Single Post

Village Idiot · 12-29-2008, 10:15 PM

Quote:

Originally Posted by Salathe

Feel free to quote me on this later, but I think that would be a huge waste of time and effort. If it comes to it, later (much, much later) we could build some bridges between our application and other applications but I don't see it as an essential, or even useful, thing to work towards right now.

Other SQL comments:

For the primary keys, use unsigned ints. Why allow for, but never use, numbers from -2147483648 to zero?
users.user_pass doesn't need to be VARCHAR(255). Depending on what hashing we'll be using, it can be CHAR(32) or something like that.
It might be worth making item_comments.item_comment_text bigger, 255 chars might seem more than enough (cf. Twitter's 140 limit) but should we really rule out those commenters who like to write long, thought-out messages?
Maybe I'm just being blind, but I don't see a common key between the items and item_comments tables.
Perhaps consolidate multiple tables. A subscription is a subscription, why have two tables for essentially the same thing? If we later want to allow many different kinds of subscriptions (follow comments for a user, item, album, new items from a user, in a album or gallery...) would we need a new table for each of those? The same goes for the *_comments tables.
Standardise the naming. Some tables are plural, others not. Some columns underscore-delimited while others mix with camelcase. Personally I'd drop the table name prefixes for the columns too: gallery_rating.gallery_rating_gallery_id vs gallery_rating.gallery_id

Sorry for being so nitpicky!

Nitpicking is the road to perfection, perfectly ok.

For the primary keys, use unsigned ints. Why allow for, but never use, numbers from -2147483648 to zero?
Good idea

users.user_pass doesn't need to be VARCHAR(255). Depending on what hashing we'll be using, it can be CHAR(32) or something like that.
When we decide on which one we will use, I will change it accordingly.

It might be worth making item_comments.item_comment_text bigger, 255 chars might seem more than enough (cf. Twitter's 140 limit) but should we really rule out those commenters who like to write long, thought-out messages?
I meant to make that text, we will have a custom settable comment char limit.

Maybe I'm just being blind, but I don't see a common key between the items and item_comments tables.
My bad, will fix.

Perhaps consolidate multiple tables. A subscription is a subscription, why have two tables for essentially the same thing? If we later want to allow many different kinds of subscriptions (follow comments for a user, item, album, new items from a user, in a album or gallery...) would we need a new table for each of those? The same goes for the *_comments tables.
I'm neutral on this issue, It's normally my style to separate tables that server different purposes, but I see no reason against consolidating them.

Quote:

Originally Posted by Orc

I'm going with InnoDB as my Database Engine since it's faster, sound good? :P

Sure, I'll make the revision to the SQL file tonight.

Standardise the naming. Some tables are plural, others not. Some columns underscore-delimited while others mix with camelcase. Personally I'd drop the table name prefixes for the columns too: gallery_rating.gallery_rating_gallery_id vs gallery_rating.gallery_id
The naming is standardized, top level names (users, items) are plural while their supporting tables (item_comments) are singular. This is because the top level table hold multiple items, while the supplementary tables hold data for a single item of their parent.

This is again a difference in our style, I like to name all my columns with the table name in them (perhaps I should camelcase the table names opposed to underscore them). I am, once again, neutral on this issue since it is a matter of style.