How to format a database with a field that has a table or list?

[L. D. James]

Can someone help me to define a format of a database, or a table that has a
field that will have tables in that field? Please look at the example and
explanation below:

Table: Roster

---------------------------------------------------
|User Name | User ID | Status | Daily Activity Log |
---------------------------------------------------
|John Doe | 1001 | Active | (Tabulated activity)|
---------------------------------------------------
|Mary Doe | 1002 |inactive| (Tabulated activity)|
---------------------------------------------------

Above is a brief description of the table. I would like for the last field
to be a table (or table like) format or list that will include a number of
fields of the users' activity. One user might have one or two items in his
list. Another person might have 10 or 100 items in their list. The
table-like list of the daily activity log column will include something like
the following:

1) Date/time of activity
2) Number of people involved in this session
3) Total time of this session
4) Location of the session
5) Session rating (completed/incomplete)

The activity log field may have 10 of these entries in one day. The next
day it might have 5 entries in this field. The following 3 or four days
there might not be any entries.

Thanks in advance for any comments on this matter. Hopefully someone will
get the gist and put a more educational description to my objective.
-- L. D. James
--
-----------------------
L. D. James
lj****@apollo3.com
www.apollo3.com/~ljames

[Bill Karwin]

L. D. James wrote:

I would like for the last field
to be a table (or table like) format or list that will include a number of
fields of the users' activity. One user might have one or two items in his
list. Another person might have 10 or 100 items in their list. The
table-like list of the daily activity log column will include something like
the following:
This should be done in a second table, which references the roster table.
1) Date/time of activity
2) Number of people involved in this session
3) Total time of this session
4) Location of the session
5) Session rating (completed/incomplete)

CREATE TABLE roster_activity (
user_id INTEGER NOT NULL REFERENCES roster(`User ID`),
activity DATETIME NOT NULL,
num_people INTEGER NOT NULL DEFAULT 1,
session_minutes INTEGER NOT NULL DEFAULT 0,
session_location VARCHAR(200) NOT NULL,
session_rating TINYINT NOT NULL DEFAULT 0
);

Now you can get all the activity for a user thus:

SELECT r.*, a.*
FROM roster AS r INNER JOIN roster_activity AS a
ON r.`User ID` = a.user_id
WHERE r.`User ID` = 1001;

This returns a separate row for each activity of that user.

Regards,
Bill K.

[L. D. James]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:d3*********@enews3.newsguy.com...

L. D. James wrote: CREATE TABLE roster_activity (
user_id INTEGER NOT NULL REFERENCES roster(`User ID`),
activity DATETIME NOT NULL,
num_people INTEGER NOT NULL DEFAULT 1,
session_minutes INTEGER NOT NULL DEFAULT 0,
session_location VARCHAR(200) NOT NULL,
session_rating TINYINT NOT NULL DEFAULT 0
);

Bill. Thanks a bunch for the quick and incrediable clear response.

-- L. James

--
--------------------------------
L. D. James
lj****@apollo3.com
www.apollo3.com/~ljames

[L. D. James]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:d3*********@enews3.newsguy.com...

L. D. James wrote: CREATE TABLE roster_activity (
user_id INTEGER NOT NULL REFERENCES roster(`User ID`),
activity DATETIME NOT NULL,
num_people INTEGER NOT NULL DEFAULT 1,
session_minutes INTEGER NOT NULL DEFAULT 0,
session_location VARCHAR(200) NOT NULL,
session_rating TINYINT NOT NULL DEFAULT 0
);

Now you can get all the activity for a user thus:

SELECT r.*, a.*
FROM roster AS r INNER JOIN roster_activity AS a
ON r.`User ID` = a.user_id
WHERE r.`User ID` = 1001;

This returns a separate row for each activity of that user.

Bill (or anyone else familiar with the Perl interface to Mysql), as I
mentioned, the formula you gave works ill (or anyone else familiar with the
Perl interface to Mysql), as I mentioned, the formula you gave works
perfect. I've created the database, and can very easily enter, maintain and
view the data. Now I'm trying to make an easy interface for others to enter
and maintain.

I installed the Mysql module to Perl. I'm able to connect to the database.
View the data and structure. However, I can't figure out the "Insert"
command or option.

Can someone give me a sample line that will insert a basically do the
following from Perl:

Mysql> INSERT INTO roster_activity (1001,DATETIME,5,100,"498 Northland
Avenue",100);

The database was opened using the following:

-----------------------------
#!/usr/bin/perl
use Mysql;

$host = 'localhost';
$database = "mybase";
$user = "user";
$password = "mypassword";

$db = Mysql->connect($host, $database, $user, $password);
$db->selectdb($database);
@tables = $db->listtables;

foreeach $table (@tables)
{
print "$table\n";
}

# Here is where I would like to insert the code to insert a new record into
# the roster_activity table.
-----------------------------

Thanks in advance for any suggestions or comments.

-- L. D. James
--
--------------------------------
L. D. James
lj****@apollo3.com
www.apollo3.com/~ljames