SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 40 of 40
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, rémon, i concede -- it's actually A LOT

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,027
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Be cautious about "id" because it is significant to javascript and you could run into this gotcha.

    Code html:
    <form id="myForm">
      <input type="hidden" name="id" value="23">
    </form>

    The form id of "myForm" gets overwritten by "23" in this form in many browsers and not a few javascript frameworks. There are multiple ways around the problem on both view and database side, but it's worth pitching this out there so you can be aware of it.

    On the subject of natural versus artificial keys - it's just too easy to get things wrong with natural keys. A key needs to be immutable and unique. About the only thing I commonly see that fits that criteria is social security numbers -- but those often need to be encrypted which makes them a poor candidate for a key.

  3. #28
    SitePoint Wizard
    Join Date
    Apr 2007
    Posts
    1,399
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I didn't read any of the post above but generally ID is better than specifying as a name. For example, let say a user has bookmarked
    http://blah?productName=ABC

    But, later that day... that productName was misspelled or changed for whatever reason then your client's bookmarked link is no longer correct. Also, it enhances a bit in security side as well. If some nasty network admin decides to sniff HTTP request then he'll see things like

    http://blah?productName=iPad2

    then it may peak the interest of hackers to hijack the session. Just my 2 cents. In general, I always use "ID" and chances are you're going to be "ID" column for some other tables anyways. So, why not make it as a standard?

  4. #29
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sg707 View Post
    In general, I always use "ID" and chances are you're going to be "ID" column for some other tables anyways. So, why not make it as a standard?
    I was with you up until this statement.

    The reason you dont 'make it a standard' is the same reason you create a synthetic key in the first place - overhead.

  5. #30
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Mansfield, UK
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I always create ID's because everything else on that row is probably data and might change. The ID is not data and can remain consistent.

    If you start using natural keys then changing the key becomes very heavy if you have references to that data elsewhere. Changing the primary key is likely to need transaction locking, and the ability to roll back if something goes wrong.

    Any time you use transactions you run the risk of a deadlock?

    If I can keep things simple I will. This might mean I have an extra integer column on my table - oh dear, how sad.

  6. #31
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,027
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    ^ heh heh, yep.

    BTW, if you don't define an index, MySQL does it anyway but hides it. Internally the system MUST have a unique identifier for all rows.

    BTW, id's should be named according to the same convention throughout the database. Further, as foreign keys they should have the same name regardless of the table they occur on.

    For instance, 'id' on table 'user'. On any other table a foreign key reference to this should be named 'userid' or 'user_id'. Be consistent with whichever name is used - don't call the foreign key 'userid' on one table 'user_id' on another, and 'usernumber' on another.

    I'm currently dealing with a database where the original programmers did crap like that. It's a huge headache.

  7. #32
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As DaveMaxwell says a URL like "http://www.sitename.co.uk/productname" would be best. If I use an ID this would be difficult if not impossible. Or is there a way to convert an ID within a URL into a product name using MOD Rewrite? I dont think it would be wise to use the 'get' function in the original URL to get both the ID and product name... To achieve the simple domain above I would need to pass the product name ONLY in the URL. Correct?

    Matt.

  8. #33
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    Or is there a way to convert an ID within a URL into a product name using MOD Rewrite?
    according to petitio principii, that wouldn't be possible unless there is a one-to-one mapping of id to product name

    in which case you have no reason to use an id in the url in the first place, just go ahead and make the product name a unique key in the table, put it into the url, and bob's your uncle

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #34
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    An issue with using ID numbers is also a problem with new entries. If I want to add a new entry into a table how easy is it to add it in between 2 current entries. Would the ID's change automatically??

    If it is not possible to add new entries between two current entries the table will look like a disorganised mess of products added at the bottom of the table as the new products get launched. What is the normal way to get around this problem when using ID's?

    Matt.

  10. #35
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    Would the ID's change automatically??
    absolutely not -- you'd have planes falling out of the sky if they did


    Quote Originally Posted by MatthewBOnline View Post
    If it is not possible to add new entries between two current entries ...
    it is, but you have to supply the id number yourself, and it can't already exist

    Quote Originally Posted by MatthewBOnline View Post
    ... the table will look like a disorganised mess of products
    my advice is, don't look at them and you'll be fine

    no, srsly

    Quote Originally Posted by MatthewBOnline View Post
    What is the normal way to get around this problem when using ID's?
    step number 1 is realizing that it is ~not~ a problem

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #36
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Mansfield, UK
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you query the database with your SELECT statement, you will add an ORDER parameter to it so that the records come back in the sequence you want and not the order they were entered in.

    for instance, SELECT id,productname,price FROM products ORDER by productname ASC

    look these up on w3schools.com if you are unfamiliar with this concept

  12. #37
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The ordering would certainly make things easier. I will probably have a column that is numbered and then I can change the order by changing the numbers. Problem with this though is if there are 30 things in the navigation bar and a new product should list first then I will need to add 1 to all 30 so that the new number 1 lists first. This is the best method I can think of though!?!!? And if the new product should appear in position 8 then the current 8 and above will need 1 adding to them, etc... a bit dodgy but it is the only way I can think of... any ideas about that??

    Secondly, can you change the structure of a table in PHPMyAdmin? If I realise I want to add a new column between column 3 and 4, or I want to move the price column from column 8 to column 5 can this be done...or is the structure unchangeable from day 1 when it is first created??

    Matt.

  13. #38
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    1. numbering -- things will go easier if you number the rows in increments of 100, then you can "move" one entry in between two others easily, without renumbering them all

    2. changes in phpmyadmin -- yes, i believe these are possible, but i gave up phpmyadmin years ago in favour of heidisql, where most things are a lot easier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #39
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Mansfield, UK
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    The ordering would certainly make things easier. I will probably have a column that is numbered and then I can change the order by changing the numbers. Problem with this though is if there are 30 things in the navigation bar and a new product should list first then I will need to add 1 to all 30 so that the new number 1 lists first. This is the best method I can think of though!?!!? And if the new product should appear in position 8 then the current 8 and above will need 1 adding to them, etc... a bit dodgy but it is the only way I can think of... any ideas about that??
    As R937 says, If you must have a sort column then if you are manually adding to the table then just leave gaps so that you can insert later. If programmatically adding then you will need to renumber all rows with a number higher than the row you are inserting.

    Quote Originally Posted by MatthewBOnline View Post
    Secondly, can you change the structure of a table in PHPMyAdmin? If I realise I want to add a new column between column 3 and 4, or I want to move the price column from column 8 to column 5 can this be done...or is the structure unchangeable from day 1 when it is first created??
    Yes, most tools allow you to alter the design of the database. The order of the columns should be irrelevant to your code. You may need to go back and fix the existing rows which will now have an empty cell in the new column.

  15. #40
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Mansfield, UK
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Matthew. You might find this course useful:

    https://learnable.com/courses/php-my...r-beginners-13


Bookmarks

Posting Permissions

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