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<br>'.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
