If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Best recommendation I can give you on the database design is to read up on "database normalization". I suspect that 99% of the time, if it logically makes sense to have 100 columns in one table, your logic is flawed -- but there may well be an exception. But even if you do have 100 columns, if you only need 3 of them for a given purpose and only do SELECT col1, col23, col55 FROM... (instead of SELECT * FROM...), you should be okay from a performance standpoint (assuming all else is good, data is nomarlized, indexes are properly applied, etc.).

"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation

Hmm, am I understanding correctly that a table that meets the requirements of 3NF has only one column that is unique? My "users_accounts" table requires userid and username to be unique. I guess it's not in 3NF?

The reason username is unique is because it's used for logging in. The login form is going to ask for the username and password. However, username is never going to be publicly displayed. That's what screen_name is for. Screen_name isn't unique.

User ID is what is used in all other cases to point to a particular user. All dogs will have a user ID listed with them to identify their owner, all items will have a user ID, all forum posts will have one, etc.

So do I need to have username and userid in different tables? Like, maybe, I could make a table called user_login_info and put userid, username, and password in there? Note: users_accounts would still have userid because that is the only way to link a row in users_accounts to one in users_login_info.

The better I get at programming, the more I appreciate arrays.Handy dandy things they are.

The user ID is what is used to identify a user in 99% of cases. The username is just used for logging in. It has to be unique but I like the idea that a player could have it changed if they, for some reason, wanted to. Since the user ID is what is identifying users most of the time, changing the username wouldn't break any links or database foreign keys.

The better I get at programming, the more I appreciate arrays.Handy dandy things they are.

#A Also, do you think I should take the activation-related columns in `users_accounts`--activation_code, and activated_or_not--and put them in their own table? That table would be called `user_activation` and have user_id, activation_code, and activated_or_not in it.

The better I get at programming, the more I appreciate arrays.Handy dandy things they are.

hello i am new on this good forum and i want some help about php script i am download one free article directory script and install but this directory not have any meta key word meta discraption meta tags etc option in admin panel please help me how to add these options first see my directory Free Article Directory
Thank you

#2 and #3 look fine to me, #1 might be ok or not, depending on the meaning/usage of some of the later fields (e.g.: Will a dog never have more than one assigned food? What goes in the genes field?).

#A: You may find this simpler to do via the DB than via PHP (or at least no more difficult), as most DMBS's have a fairly rich set of "date arithmetic" functions. If using a reasonably recent version of PHP, look at the DateTime and DateInterval objects for doing this on the PHP side.

"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation

(e.g.: Will a dog never have more than one assigned food? What goes in the genes field?)..

Your first question: Only one assigned food per dog.

Your second question: A string that contains the dog's genes separated by commas. It'd be like, I don't know, maybe 250 characters long. As for if it changes, a dog's genes are determined at birth and never change.

Actually now that I think about it, a dog will have two fields for genes: coat_color_genes and regular_genes.

The better I get at programming, the more I appreciate arrays.Handy dandy things they are.

If all you ever care about is the complete gene "string", then it is OK, but if you want to pick out, say, the 44th character for some reason, then strictly speaking it's not normalized, although it might be a case where you consider it an acceptable situation if it's something you'll never have to search on (e.g., select all dogs where the 44th gene element is "A" and the 53rd element is "G", as it would require a full table scan to locate them (indexes wouldn't be useful).

"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation

The only queries I'll be doing on the gene columns are ones that want the entire string. When two dogs are bred the parents genes are going to be pulled, in their entirety, from the database and each puppy will get a random 50% of daddy's genes and 50% of mommy's (well genes are more complicated than that but I'm keeping the explanation simple). Anyway, I do this with PHP. I explode the strings into arrays and go from there. So anyways, I think my gene data is normalized? I think of a gene string as a whole entity. There's no reason to store the genes separately. It'd take up more space and PHP would still be making an array out of the genes for the breeding.

Anyhoo, I've collected some questions over the past few days.

#1 How does indexing a unique column help speed up queries? The index would have just as many entries as there are rows in the table.

#2 Should I use indexes to enforce game rules? For example, no two dogs can have the same registered name if they're in the same breed. If they're different breeds, though, it's ok. So if we have a Doberman Pinscher and Great Dane named "Coco's Chocolate Delight", that's fine, but if there were two Doberman Pinschers named that, that would not be ok. Now, here's where the water gets a litlte muddy. I store the prefix part of a dog's name and the suffix of a dog's name separately. In "Coco's Chocolate Delight", "Coco's" is the prefix and the rest is the suffix. A prefix is just to show what kennel a dog came from so there is no limit to how many dogs can have it. So a Doberman Pinscher named "Coco's All that Glitters" and another Doberman named "Coco's Chocolate Delight" is fine. Why do I store the prefix separately? Because it's never allowed to be change. When somebody wants to change their dog's registered name, it only lets them change the suffix. It's easy to let PHP know what is prefix and what is suffix if I just keep it separate. So, I made a unique index on breed_id, prefix, and registered_name. Is this ok or should I not be using suffixes to enforce my game rules?

#3 This is probably a stupid sounding question but is running my input values through a form validator and putting quotes around everything enough to keep SQL injection out of my queries? e.g.

#4 Lastly, I have a sidebar on my site that displays different content depending on if the user is logged in or not. I'm going to need an if statement that basically tells PHP "if user is logged in, display this stuff, otherwise, display this other stuff." That's not a problem but the thing is I don't know where to put the code. It can't go in a controller, cause you can only load one controller with the URL, and this sidebar displays on every page. I don't think if statements go in the view, so I don't think I can just make a sidebar.php file for the view and do the if statement there. So where would I do this?

Last edited by evenstar7139; 09-09-2012 at 12:02 AM.

The better I get at programming, the more I appreciate arrays.Handy dandy things they are.

1. Indexing creates some sort of pre-sorted structure that the DBMS can use to help it figure out which rows to check when looking for values in the indexed column[s]. I don't know the precise mechanism, but it will be sort of like: if the value at index X is too large, then don't even bother looking at any rows where that index is higher -- and the same thing for too low.

2. Sounds like a 3-column unique index to me.

3. Use prepared statements and bound parameters as a first option (e.g. with PDO or MySQLi), then use the DB extension-specific escaping mechanism as a second option. No, form validation is not enough, since there will, for example, likely be fields where an apostrophe should be allowed, yet it needs to be escaped when being used in the SQL for most DBMS's.

4. Sounds fine in the view to me. You could either put the if/else logic right in the view, or have it call some sort of helper function that would handle the logic, e.g.:

PHP Code:

<?php echo getSidebar($isUserLoggedIn); ?>

"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation