Programming Forums > Script Programming > Other Scripting Languages

programmingnoob · Aug 21st, 2007, 7:45 PM

how do they work?
so i saw someone posting like...

Create Table login
    (Id int(4) NOT NULL,
    UserName varchar(20) NOT NULL,
    Password varchar(6) NOT NULL,
    securityQuestion varchar(50) NOT NULL,
    SecurityAnswer varchar(50) NOT NULL,
    Primary key (Id));

This person is clearly using numeric key...
but how do they work?
If I need information regarding a certain person's username and password... how is numeric key going to help the query?
any help would be greatly appreciated

DaWei · Aug 21st, 2007, 7:54 PM

It isn't, particularly. Suppose, however, that you had all the user information (name, address, etc.) in one table and the password and security question/answer in another table. The efficient link would be via key. Joe Blow has the key 10001 in the user table and Joe Blow's password record has the key 10001 in the passwords table.

You will be more efficient if you learn databases via tutorial material (books, etc.) than if you learn them by inspecting existing databases.

Start with relational databases and don't forget to check out normalization.

lectricpharaoh · Aug 22nd, 2007, 5:20 PM

I suggest you follow DaWei's advice and learn about relational databases, and normalizing the data.

The basic ideas here are to reduce redundant information, and make the system more extensible. For example, I once heard a horror story from someone where they were required to maintain a table for something or other. The original designer of the database (not the person relating the story) had come up with the brilliant solution of having the years as columns in one big table. This meant every year, they had to add a new column to the database, and modify all the queries.

With a relational database, it should have been designed such that some of the information was in one table, and then a second table would contain a year column, and a column of foreign keys. The foreign keys are essentially pointers to primary keys in the first table. Then, by searching the second table for all records where the year column matches what you're looking for (whether exact or in a range, such as 'all records from 2005 or earlier'), you have a list of all records from the first table where the year is what you want. You could then iterate through the first table, or better yet, you could design your query to do both operations (more efficient this way, as a) you only call the database once, and b) you take advantage of the many optimizations built into the DBMS software).

A better example might be a company that sells stuff, say books. They have a list of invariant information for each book, such as ISBN, author, title, publisher, and date published. Then they have a second table with the variable information, such as number of copies in stock, number of copies on order, supplier, price, etc.

Now imagine this company wants to track what books customers buy (there are many reasons for this, ranging from buyer incentive programs to targeted advertising). Thus, they have a third table for customers. Now, one way to design this table would be something like this:

(Toggle Plain Text)

Name     Purchase1    Purchase2    Purchase3    Purchase4

As you can probably see, this is a totally stupid design; if a customer purchases fewer than four titles, space is wasted, and if they purchase five or more, some purchases cannot be recorded. Thus, a better solution would be to add a table of purchases, with each purchase containing both a customer ID value, and an identifier for what was purchased (such as ISBN).

The above is intentionally simplified. A real system would likely have an intermediary table (call it 'transactions') between the customer and the purchase, because a customer can buy several items at once. When you start reading about the different relationships, you will see why this is necessary. You cannot directly have a many-to-many relationship in a relational database; instead, you have a many-to-one and a one-to-many (with the 'one' being the same between the two).

Anyways, read up on it. An understanding of the basic principles of modern relational databases is almost essential in the programming world today, even if learning SQL isn't (depends on what you want to do).

Aug 21st, 2007, 7:45 PM	#1
programmingnoob Hobbyist Programmer Join Date: Feb 2006 Posts: 154 Rep Power: 3	mysql - question about numeric key how do they work? so i saw someone posting like... (Toggle Plain Text) Create Table login (Id int(4) NOT NULL, UserName varchar(20) NOT NULL, Password varchar(6) NOT NULL, securityQuestion varchar(50) NOT NULL, SecurityAnswer varchar(50) NOT NULL, Primary key (Id)); Create Table login (Id int(4) NOT NULL, UserName varchar(20) NOT NULL, Password varchar(6) NOT NULL, securityQuestion varchar(50) NOT NULL, SecurityAnswer varchar(50) NOT NULL, Primary key (Id)); This person is clearly using numeric key... but how do they work? If I need information regarding a certain person's username and password... how is numeric key going to help the query? any help would be greatly appreciated

Aug 21st, 2007, 7:54 PM	#2
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	It isn't, particularly. Suppose, however, that you had all the user information (name, address, etc.) in one table and the password and security question/answer in another table. The efficient link would be via key. Joe Blow has the key 10001 in the user table and Joe Blow's password record has the key 10001 in the passwords table. You will be more efficient if you learn databases via tutorial material (books, etc.) than if you learn them by inspecting existing databases. Start with relational databases and don't forget to check out normalization. __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Aug 22nd, 2007, 5:20 PM	#3
lectricpharaoh Caffeinated Neural Net Join Date: Jun 2005 Location: Dry west coast of Canada Posts: 927 Rep Power: 4	I suggest you follow DaWei's advice and learn about relational databases, and normalizing the data. The basic ideas here are to reduce redundant information, and make the system more extensible. For example, I once heard a horror story from someone where they were required to maintain a table for something or other. The original designer of the database (not the person relating the story) had come up with the brilliant solution of having the years as columns in one big table. This meant every year, they had to add a new column to the database, and modify all the queries. With a relational database, it should have been designed such that some of the information was in one table, and then a second table would contain a year column, and a column of foreign keys. The foreign keys are essentially pointers to primary keys in the first table. Then, by searching the second table for all records where the year column matches what you're looking for (whether exact or in a range, such as 'all records from 2005 or earlier'), you have a list of all records from the first table where the year is what you want. You could then iterate through the first table, or better yet, you could design your query to do both operations (more efficient this way, as a) you only call the database once, and b) you take advantage of the many optimizations built into the DBMS software). A better example might be a company that sells stuff, say books. They have a list of invariant information for each book, such as ISBN, author, title, publisher, and date published. Then they have a second table with the variable information, such as number of copies in stock, number of copies on order, supplier, price, etc. Now imagine this company wants to track what books customers buy (there are many reasons for this, ranging from buyer incentive programs to targeted advertising). Thus, they have a third table for customers. Now, one way to design this table would be something like this: (Toggle Plain Text) Name Purchase1 Purchase2 Purchase3 Purchase4 Name Purchase1 Purchase2 Purchase3 Purchase4 As you can probably see, this is a totally stupid design; if a customer purchases fewer than four titles, space is wasted, and if they purchase five or more, some purchases cannot be recorded. Thus, a better solution would be to add a table of purchases, with each purchase containing both a customer ID value, and an identifier for what was purchased (such as ISBN). The above is intentionally simplified. A real system would likely have an intermediary table (call it 'transactions') between the customer and the purchase, because a customer can buy several items at once. When you start reading about the different relationships, you will see why this is necessary. You cannot directly have a many-to-many relationship in a relational database; instead, you have a many-to-one and a one-to-many (with the 'one' being the same between the two). Anyways, read up on it. An understanding of the basic principles of modern relational databases is almost essential in the programming world today, even if learning SQL isn't (depends on what you want to do). __________________ A man's knowledge is like an expanding sphere, the surface corresponding to the boundary between the known and the unknown. As the sphere grows, so does its surface; the more a man learns, the more he realizes how much he does not know. Hence, the most ignorant man thinks he knows it all. - L. Sprague de Camp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Virtual pet site MySQL question	proudnerd	PHP	6	May 13th, 2006 7:22 PM
Another MySql question...	TCStyle	PHP	1	Nov 30th, 2005 6:04 AM
another simple question about PHP and mySQL	HelloWorldProgram	PHP	6	Jul 10th, 2005 6:40 PM
Very simple question about mysql	HelloWorldProgram	PHP	3	Jul 8th, 2005 10:42 AM
Mysql search question?	dmorales	PHP	15	Apr 25th, 2005 2:08 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Script Programming > Other Scripting Languages

mysql - question about numeric key