At 13:42 11-11-2004, Sebastian Mendel wrote:
// send Messages
$sql = '
INSERT INTO `hf_privmessages`
(`ownerID`,`fromID`,`toID`,`from_name`,`to_name`,`priority`,`date`,`subject`,`message`,`folder`)
SELECT `userID` AS `ownerID`,
' . (int) $userID . 'AS `fromID`,
`userID` AS `toID`,
"' . $username . '" AS `from_name`,
`nickname` AS `to_name`,
"' . $_POST['priority'] . '" AS `priority`,
NOW()AS `date`,
"' . $subject . '" AS `subject`,
"' . $message . '" AS `message`,
"inbox" AS `folder`
WHERE `userID` IN (' . implode( ', ', $_POST['rcvID'] ) . ')
';
mysql_query ... ;
// place copy
$toID = implode(',',$_POST['rcvID']);
if ( count($_POST['rcvID']) > 1 )
{
$to_name = 'multiple';
}
else
{
// get recipient name
$to_name = 'multiple';
}
$msgquery = "INSERT INTO hf_privmessages
(`ownerID`,`fromID`,`toID`,`from_name`,`to_name`,`is_read`,`is_sent`,`priority`,`date`,`subject`,`message`,`folder`)
VALUES
('$userID','$userID','$toID','$username','$to_name','1','1','".$_POST['priority']."',NOW(),'$subject','$message','sent')";
$result = mysql_query($msgquery) or die('Unable to insert
message'.mysql_error());
your databes lags normalization!!!
My lack of normalization is open for debate, the tables are designed this
way to suit the flexibility I need ... the reason I have the ownerID in the
table is simply to make the code easier for me to read. I do know it's not
strictly needed because I can find the right owners by using the read and
sent flags, but it made the code harder for me to read when I did it like
that. Because this project is nowhere near finished, I need to keep the
code a little easier to read, at the cost of a little performance. I'm
aware that having the to/from names with the messages don't look
immediately like a good idea, because it adds apparently superflous data to
the table, but it serves a specific purpose. If I didn't have the names
with the messages, then I'd have to use joins to get those in when pulling
the messages from the database. That in itself isn't the problem. The
problem comes with deleting users. If a user is deleted, there's no
information to attach to the corresponding ID in the message, and thus the
join would omit any messages sent to/from users no longer in the system,
and in the case of userID recycling, there's the risk of it appending the
wrong username to the message.
The sheer number and size of the fields in the userprofiles in this system
makes it unpractical to simply mark them as deleted when deleting them. It
would mean a serious waste of diskspace that goes beyond what the little
lack of normalization in this one table causes. I have the username
"fallback" fields in 3 of 14 tables, with the planned design that will
eventually become around 20-22 total tables, because of alot of needed
cross-linking between tables. It goes back to the practicality of not
keeping user profiles in the system for any other purpose than making joins
work.
I'm by far not an expert on neither PHP nor SQL or database design, and
never claimed to be. I learned to program when I was 8, in Basic, and
learned about databases by fooling around with MS Access. It's been 20
years, and till last fall I've mostly never touched creating or designing
databases at all cuz I've mostly worked with the HTML side of things, and
otherwise only done coding for databases made by others, where I've had no
say in how the database was organized.
Everything I do these days is about learning how to push the limits of what
the code and the database allows. And to learn how to get the functionality
I want in the most efficient and practical way. Slandering my work will not
do me any good. If you wanna come with constructive criticism that's fine,
but have you ever considered that there are reasons for everything ? If you
don't know the reason for the design choice, or the whole picture, any
criticism you make will always and only be objective, and not constructive.
But most the entire database have fields cut back to make the normalization
as great as possible. I'm still largely working on getting all the
functionality working primarily, and thus I'm still doing some
experimentation with the database design, as in what fields I need and
don't need to do what I need to do. The 3 tables that aren't fully
normalized contain posts and messages in various forms. For the simple
reason of being able to display those posts/messages, and do so with the
correct original username, I slack a little on the normalization to make
that work.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
