SQL Databases

[Archangel]

I'm starting to learn how to use SQL databases and i was wondering if its ok to have 2 tables for the users infromation, for example:

Having table: USERS with the rows: USER_ID USERNAME PASSWORD
and then having: USERS_INFO with the rows: USERS_INFO_ID ADRESS PHONE

the idea is having 2 separate tables to manage the users information, is this a correct way of doing this?

If so, what command should i be using to request the user adress using the user_id?

Edit: im new to this theme, so dont go hard with me .

[Barabajagal]

One table, multiple fields.

[Archangel]

One table, multiple fields.

k, thanks :]. I will post if i got any more doubts.

[FrOzeN]

It really depends on the data you'll be storing. You have to determine how flexible your database needs to be. If you just wanted to know the user's main address, then putting it all into one table would probably be sufficient. But say the information you were collecting required room to allow for client's that have two or more addresses. In that scenario you would create a second table which stored all the address' and referenced each address to a ClientID.

[MyndFyre]

This is a process we call "normalizing a database."  There's a pretty great Wikipedia article about it and lots of Google pages.

Basically, you should take a look at the logical objects that you'll be storing; where there are relationships of 1:n or m:n, these are almost always prime candidates for normalization.

In your user table, for instance, consider that you might want to store a list of user's IM contact info.  So, consider that a user object might be represented:

Code Select Expand


class CUser
{
    public:
        const std::string& GetUserName() const;
        const std::string* GetProfileIMLinks() const;
        const int GetProfileIMLinkCount() const;
    private:
        // etc
}

This class represents a user that has multiple IM links in his profile.  Your software might define an IM link as a hypertext link that can include the user's name.  For instance, all AIM links might be "aim:goim?user=%%USERNAME%%

A database schema for this might looks something like this (this is T-SQL for MS-SQL products):

Code Select Expand


CREATE TABLE [Users]
{
    UserID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    UserName nvarchar(75) NOT NULL
}

CREATE TABLE [IMLinks]
{
    ProtocolID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    ProtocolName nvarchar(10) NOT NULL UNIQUE,
    ProtocolLinkFormat nvarchar(125) NOT NULL
}

CREATE TABLE [UserIMs]
{
    UserID int NOT NULL PRIMARY KEY REFERENCES [Users](UserID) ON DELETE CASCADE,
    ProtocolID int NOT NULL PRIMARY KEY REFERENCES [IMLinks](ProtocolID) ON DELETE CASCADE,
    UserName nvarchar(30) NOT NULL
}


With these three tables you have everything you need to have unlimited IM protocol fields and unlimited IM profiles per user.  Consider:
Users:
1  MyndFyre 
2  Archangel

IMLinks:
1  AIM  aim:goim?userid=%%USERNAME%%
2  MSN  http://members.msn.com/%%USERNAME%%
3  GTalk  googletalk:talk?userid=%%USERNAME%%   (Note: I have no idea if these work)

UserIMs:
1  1  MyndFyre
1  2  [email protected]
1  3  [email protected]
2  2  [email protected]

Do you see how there are links by ID across the different tables?  These relational links allow us to make sure that our database integrity is good as well as avoid repeating information.  NOTE: Although UserIMs has "[email protected]" for both 1/2 and 1/3, realize that this information is actually separate.  They're separate addresses, one for MSN and one for Google Talk.

[Banana fanna fo fanna]

i wouldn't use sql if i were you. the technology is outdated and is barely in use anymore. only a few vendors support the technology until its end of life later this year. no real company still uses a sql database these days. sql is slow, inflexible, and overall lacking in relevance to any career plans you may have.

instead, i would use ajax.

[iago]

i wouldn't use sql if i were you. the technology is outdated and is barely in use anymore. only a few vendors support the technology until its end of life later this year. no real company still uses a sql database these days. sql is slow, inflexible, and overall lacking in relevance to any career plans you may have.

instead, i would use ajax.

Just to be clear (for newbies), that post didn't make sense.

[Invert]

i wouldn't use sql if i were you. the technology is outdated and is barely in use anymore. only a few vendors support the technology until its end of life later this year. no real company still uses a sql database these days. sql is slow, inflexible, and overall lacking in relevance to any career plans you may have.

instead, i would use ajax.

^
|
This is the dumbest thing I have ever read on the Web Development forums.

[Banana fanna fo fanna]

im super serial guys.

[Don Cullen]

i wouldn't use sql if i were you. the technology is outdated and is barely in use anymore. only a few vendors support the technology until its end of life later this year. no real company still uses a sql database these days. sql is slow, inflexible, and overall lacking in relevance to any career plans you may have.

instead, i would use ajax.

^
|
This is the dumbest thing I have ever read on the Web Development forums.

Seconded.

[Grok]

i wouldn't use sql if i were you. the technology is outdated and is barely in use anymore. only a few vendors support the technology until its end of life later this year. no real company still uses a sql database these days. sql is slow, inflexible, and overall lacking in relevance to any career plans you may have.

instead, i would use ajax.

   

Nonsense.

[bulletproof tiger]

What does that even mean...

[macyui]

i wouldn't use sql if i were you. the technology is outdated and is barely in use anymore. only a few vendors support the technology until its end of life later this year. no real company still uses a sql database these days. sql is slow, inflexible, and overall lacking in relevance to any career plans you may have.

instead, i would use ajax.

uh, i work at zynga and we use the following:
mysql database
memcache
mongoDB for persistence
php to wrap facebook api
java and smartfox for socket server
flash client with some javascript


what is wrong with mysql btw?

[rabbit]

Dunno.  AJAX doesn't replace a database anyway....

[smithshn]

You can use the normalization. Normalization can sort the data and it manage the database properly.Therefore, you can access your database as fast in minimum duration by query.