Phpmyadmin - Need Help With Creating Tables And Fields

[gameboyz]

This ID column is supposed to be the unique ID of every note a user post. The website I'm designing is a website that lets people post notes and stuff for discussion, etc. But I dunno what does "Length/Values" means and what I should put for it. I think I should use BIGINT and auto_increment because of large numbers and unique IDs, but I have no idea about the rest.

[justsomeguy]

Check the MySQL documentation for the options for creating a table:http://dev.mysql.com/doc/refman/5.1/en/create-table.htmlThis might also be helpful:http://www.webhosting.uk.com/phpmyadmin-tutorials.php

[gameboyz]

Check the MySQL documentation for the options for creating a table:http://dev.mysql.com/doc/refman/5.1/en/create-table.htmlThis might also be helpful:http://www.webhosting.uk.com/phpmyadmin-tutorials.php

I still dun really understand what should be entered for the "Length" field. According to the 2nd link it says that is the number of characters the entry will have. If I dunno how many IDs will I need in total what do I put?And what's the difference between null and 0?And what about auto_increment?

[Synook]

How many IDs do you think you will need? 100? 1000? 1000000000000? It all depends on what the table's contents will be.NULL is nothing, i.e. no value. 0 is the decimal number "zero". 0 != NULL.auto_increment automatically generates new values for each new record.

Edited July 14, 2009 by Synook

[gameboyz]

How many IDs do you think you will need? 100? 1000? 1000000000000? It all depends on what the table's contents will be.NULL is nothing, i.e. no value. 0 is the decimal number "zero". 0 != NULL.auto_increment automatically generates new values for each new record.

If the length/values is, say "3", so after ID 999 I can't insert any more rows without duplicating IDs?And what's the difference between primary, index and unique keys? I googled for it but still don't really get it

[Ingolme]

The primary index is the field that you're planning on using to search for database rows with.A unique key is a field that cannot have two duplicate rows.

[justsomeguy]

Length doesn't matter for number fields, only the data type matters. Check this page to see how big the various data types are:http://dev.mysql.com/doc/refman/5.0/en/sto...quirements.htmlAn int is a 4-byte number, so it's 32 bits, or up to 4.2 billion. That should be enough for your IDs. When defining number columns, keep in mind whether the column needs to hold negative numbers also or only positive. An ID will typically not be negative unless you're doing strange things. For columns that won't have negative values, give them the unsigned attribute. A signed int will go from -2.1 billion to 2.1 billion, and an unsigned int will go from 0 to 4.2 billion. If you don't need negatives in the column, making it unsigned will double the range. You'll also notice that the length will change if the column is signed or unsigned. A signed int shows a length of 11, unsigned is 10. But like I said, don't worry about setting the lengths for numbers, it will do that for you.About the keys, like Ingolme said a unique key just means that you can't have more than one row with the same value in the key. An index is a special type of key that helps a lot with efficient lookups. When you make indexes the database will keep index tables and pages that it groups the data in, so that when you look up records based on an index (when you have an index in the WHERE clause of a SELECT statement) it will find those records a lot faster than if you had used a non-index column for the lookup. Indexes should be used on columns that you will look up frequently, but don't overdo indexes, each index you make adds time for when you insert or update.A primary key is an index that is also unique. You can think of a primary key as the main ID for a table, I usually set up all of my tables with an int primary key auto increment ID column, and then whatever other data they need. Even my users table has both an int auto increment ID which is the primary key, plus a varchar username column which is an index for lookups (which you could make unique if you want the database to enforce that). Each table can only have one primary key, but you can make the primary key over more than one column. If you have a table that holds parent/child relationships, where it has one int column for the parent ID and one int column for the child ID, it wouldn't make sense to make either of them the primary key (parents might have more than one child or children might have more than one parent), but you can make a primary key that covers both columns, so that you wouldn't be able to add duplicate records where both the parent and child are the same as another record. You can also make indexes over more than one column if you typically search for multiple columns at once.Googling and reading a few tutorials online might not be enough to understand efficient database design, it will probably take some study with books and experience before you understand some of the subtleties.

Edited July 14, 2009 by justsomeguy

[gameboyz]

Length doesn't matter for number fields, only the data type matters. Check this page to see how big the various data types are:http://dev.mysql.com/doc/refman/5.0/en/sto...quirements.htmlAn int is a 4-byte number, so it's 32 bits, or up to 4.2 billion. That should be enough for your IDs. When defining number columns, keep in mind whether the column needs to hold negative numbers also or only positive. An ID will typically not be negative unless you're doing strange things. For columns that won't have negative values, give them the unsigned attribute. A signed int will go from -2.1 billion to 2.1 billion, and an unsigned int will go from 0 to 4.2 billion. If you don't need negatives in the column, making it unsigned will double the range. You'll also notice that the length will change if the column is signed or unsigned. A signed int shows a length of 11, unsigned is 10. But like I said, don't worry about setting the lengths for numbers, it will do that for you.About the keys, like Ingolme said a unique key just means that you can't have more than one row with the same value in the key. An index is a special type of key that helps a lot with efficient lookups. When you make indexes the database will keep index tables and pages that it groups the data in, so that when you look up records based on an index (when you have an index in the WHERE clause of a SELECT statement) it will find those records a lot faster than if you had used a non-index column for the lookup. Indexes should be used on columns that you will look up frequently, but don't overdo indexes, each index you make adds time for when you insert or update.A primary key is an index that is also unique. You can think of a primary key as the main ID for a table, I usually set up all of my tables with an int primary key auto increment ID column, and then whatever other data they need. Even my users table has both an int auto increment ID which is the primary key, plus a varchar username column which is an index for lookups (which you could make unique if you want the database to enforce that). Each table can only have one primary key, but you can make the primary key over more than one column. If you have a table that holds parent/child relationships, where it has one int column for the parent ID and one int column for the child ID, it wouldn't make sense to make either of them the primary key (parents might have more than one child or children might have more than one parent), but you can make a primary key that covers both columns, so that you wouldn't be able to add duplicate records where both the parent and child are the same as another record. You can also make indexes over more than one column if you typically search for multiple columns at once.Googling and reading a few tutorials online might not be enough to understand efficient database design, it will probably take some study with books and experience before you understand some of the subtleties.

but I can't add a BIGINT data type field without setting it?

[justsomeguy]

I don't know why that would be, I can add a bigint column using phpMyAdmin without specifying the length. Once it adds it I see that the length gets set to 20, which matches the bigint storage requirements of 8 bytes per field.