Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts

    Can i have a table inside a table

    Before trying to figure this out on my own im curious if i can have a table inside a table, so e.g. users table, different rows containing different users details, then on each row there could i have a table containing things like log in dates? Or is that just not possible?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    No. But you can have *RELATED* tables. which do exactly what you want. And they are the entire reason that RELATIONAL database systems are named...what else?..."relational".

    Example:
    Code:
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        firstName VARCHAR(30),
        lastName VARCHAR(30),
        email VARCHAR(100),
        password VARCHAR(30)
        );
    
    CREATE TABLE userDetails (
        userid INT REFERENCES users(userid),
        detailType ENUM( hobbies, interests, games ),
        detailDescription VARCHAR(500)
        );
    The combination of a PRIMARY KEY in the users table with the foreign key created via REFERENCES causes these tables to be RELATED.

    *IF* you use INNODB for your storage engine (most people do), then you will *NOT* be able to create a record in the userDetails table with an id that does not exist in the users table. And, on the flip side, you won't be able to DELETE a record in the users table if any record in userDetails has a reference to its id.

    The latter restriction can be modified by use of CASCADE DELETE, but that's a topic for another day.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    By the by, if you used an object-oriented database system (ODBMS, in contrast to RDBMS) then you could do what you asked...except that "tables" no longer exist in the RDBMS sense of the word and become, instead, simply arrays.

  • #4
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    im gonna try the first method first see how that works for me, i tried your code but it spat me the error.

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hobbies, interests, games), detailDescription VARCHAR(500) )' at line 3
    CREATE TABLE userDetails(
    userid INT REFERENCES users( userid ) ,
    detailType ENUM( hobbies, interests, games ) ,
    detailDescription VARCHAR( 500 )
    )


    Im a novice at actually writing the create table coding, i use phpmyadmin to make tables etc (i really need to learn how to type the coding), can you see the error anywhere?

    I dunno if this helps but "MySQL version 5.1.56-community-log". That's what my host is running
    Last edited by tomharto; 06-21-2011 at 07:37 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    SO SORRY!

    My error.

    ENUM values are *strings* and need apostrophes around them:

    Code:
    CREATE TABLE userDetails (
        userid INT REFERENCES users(userid),
        detailType ENUM( 'hobbies', 'interests', 'games' ),
        detailDescription VARCHAR(500)
        );
    Brain fart.

  • #6
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Thanks, that worked . If i detailed roughly what im wanting to do with this database would be be able to give me a little more indepth help with it?

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Can try. And others here will be able to help, as well. What you are doing is a very very common scenario with relational databases. Honest.

  • #8
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Well i want a standard user table, username, password, email etc, then i want the other table, everytime they log in to store there IP address and the time of the log in. Im gonna have a go now at editing the code you gave me earlier try and get that correct.

  • #9
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Okay i have this which worked

    Code:
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        firstName VARCHAR(30),
        lastName VARCHAR(30),
        email VARCHAR(100),
        password VARCHAR(30)
        );
    
    CREATE TABLE userDetails (
        userid INT REFERENCES users(userid),
        IP VARCHAR(100),
        Time VARCHAR(100)
        );
    Now, how do i use this? Is it 2 querys to insert the relevant data or do i do it all in one?

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Two queries.

    But remember, you will only insert into USERS table the *FIRST TIME* you add a new user.

    After that, you will only insert into userDetails table.

    This is called a "many-to-one" table: Many records in userDetails that relate to one record in users.

    To get back all the related records, you will use a JOIN query.

    Example:
    Code:
    SELECT users.*, userDetails.IP, userDetails.Time
    FROM users INNER JOIN userDetails
    ON users.userid = userDetails.userid
    WHERE userDetails.Time > DATE_SUB( NOW(), INTERVAL 1 DAY )
    That would get you all users, *with* their details, who have a TIME value within the last 24 hours.

    ***********

    NOTE: You are almost surely making a *HUGE* mistake having your TIME field be VARCHAR(100). If that field really is expected to hold a date and time value, then it *MUST* be declared as DATETIME, instead.

    Also, TIME is a builtin function in MySQL. You *can* use it as a field name, but it's better to avoid it.

    You might do something like:
    Code:
    CREATE TABLE userDetails (
        userid INT REFERENCES users(userid),
        IP VARCHAR(100),
        LoginTime DATETIME
        );
    Or use a name that represents whatever the real purpose of the field is.

  • #11
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Yeah i will change it to a DATETIME, that was a brainfart on my part :P. Thanks for your help, will come in userful . Whats the benefit of doing it this way rather than two completely separate tables?

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    ??? They *ARE* completely separate tables. They just happen to have a *RELATIONSHIP* defined between them.

    If you omit the REFERENCES users(userid) then they *WILL* be completely separate.


    Doesn't mean you can't still treat them as related (and, indeed, if you use MYISAM storage engine instead of INNODB, then the REFERENCES has no effect! it is treated as a comment and is not enforced).

    But if you do use INNODB and do use REFERENCES, then you get the protection of not being able to add a record to the dependent table (userDetails in your case) when the corresponding record in the independent table (users) doesn't already exist. *AND* (perhaps more importantly) you can't delete a record from the independent table if there is some record in the dependent table that *needs* it to exist.

  • #13
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts
    I phrased the question badly >.<. I meant rather what does the REFERENCES part do to how the table works, but i think i use MYISAM so i guess it does nothing but a comment :P.

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Unless you are truly using MILLIONS or records in a high performance system, you probably should switch over to using INNODB. It's a much safer engine for most purposes.

    Put it this way: You would *probably* need to be getting over 10,000 page hits per hour before it would be important to use MyISAM.

  • Users who have thanked Old Pedant for this post:

    tomharto (06-21-2011)

  • #15
    New Coder
    Join Date
    May 2011
    Location
    NYC
    Posts
    28
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Just happened to stumble on this thread and got to wondering, why couldn't you have the 'userIP' and 'lastlogindate' (for example) in the user table?

    A user has one ip, which can be updated on log in, and the 'lastlogindate' can be updated as well. I'm missing the point for an extra table here?
    If you wanted to keep track of users' logins and the IPs they've logged in from then I get it, but to store one ip and one date?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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