www.webdeveloper.com
Page 10 of 14 FirstFirst ... 89101112 ... LastLast
Results 136 to 150 of 198

Thread: My questions thread.

  1. #136
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    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

    eBookworm.us

  2. #137
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    *went and read up on the topic*

    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.

  3. #138
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    If user name is the unique identifier, then you don't really need a user ID field. That being said, if you get everything to 2NF, I wouldn't sweat that detail too much if it's problematic for you.
    "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

    eBookworm.us

  4. #139
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    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.

  5. #140
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Can you tell me which of these are 3NF compliant and which are not?

    #1 `dogs` table has dog_id, owner_id, breed_id, registered_name, gender, fixed_or_not, assigned_leash, assigned_collar, assigned_food, genes, and I'll probably add more columns.

    #2 `breeds` table has breed_id, breed_name, group_id, breed_description

    #3 `breeds_groups` has group_id, group name, group_description

    - - -

    #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.

  6. #141
    Join Date
    Sep 2012
    Posts
    8
    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

  7. #142
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Umm, if you want to ask for help, I think it would be best to start your own thread: http://www.webdeveloper.com/forum/ne...newthread&f=16
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  8. #143
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by evenstar7139 View Post
    Umm, if you want to ask for help, I think it would be best to start your own thread: http://www.webdeveloper.com/forum/ne...newthread&f=16
    thank you for your good help and i maked new topic for this.

  9. #144
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Can you guys tell me which of these are 3NF compliant and which are not?

    #1 `dogs` table has dog_id, owner_id, breed_id, registered_name, gender, fixed_or_not, assigned_leash, assigned_collar, assigned_food, genes, and I'll probably add more columns.

    #2 `breeds` table has breed_id, breed_name, group_id, breed_description

    #3 `breeds_groups` has group_id, group name, group_description

    - - -

    #A How can I check if a user is under 13 years of age, based on their birthday? I'm thinking its something like if mktime(now()) - mktime(their bday) >= mktime(13 years)
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  10. #145
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Bumping my thread because it got lost in the update. Hope nobody minds

    Ah...how to make this a more pointful post....how about this. PHP Rocks!!!
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  11. #146
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    #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

    eBookworm.us

  12. #147
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Quote Originally Posted by NogDog View Post
    (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.

  13. #148
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    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

    eBookworm.us

  14. #149
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    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.
    PHP Code:
    $this->db->query("SELECT `user_id` FROM `table_name` WHERE `login_name` = '".$this->input->post('login_name')."' AND `login_name` = '".$this->input->post('password')."'"); 
    (the form validation part is omitted)

    #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.

  15. #150
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    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

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles