markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 can you put your admin table query here too - thanks. Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 can you put your admin table query here too - thanks. do you mean the admin table? Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 yes please Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 yes please my table called user not admin, I log users to the admin area based on their access level... I have a table called user_group that has a fk in the user table. users activate thier membership by an activation link sent to thier email upon registration here is the table CREATE TABLE IF NOT EXISTS `user`( UserID INT UNSIGNED AUTO_INCREMENT, UserName VARCHAR(75) NOT NULL, FirstName VARCHAR(60) NOT NULL, LastName VARCHAR(60) NOT NULL, Email VARCHAR(100) NOT NULL, Password CHAR(40) NOT NULL, PasswordLastUpdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, Gender CHAR(1), DOB DATE, RegistrationDate DATETIME, LastVisit DATETIME, RegistrationKey VARCHAR(32) NULL, TemporaryPassword VARCHAR(55), IsActivated TINYINT UNSIGNED DEFAULT 0, ReceiveEmail TINYINT UNSIGNED DEFAULT 1, IP CHAR(15), GroupID INT UNSIGNED NOT NULL COMMENT 'Fk to the usergroup', CountryID INT UNSIGNED NOT NULL COMMENT 'Fk to the country table', CONSTRAINT pk_user_UserID PRIMARY KEY(UserID), CONSTRAINT fk_usergroup_user_groupID FOREIGN KEY(GroupID) REFERENCES usergroup(GroupID), CONSTRAINT fk_country_user_countryID FOREIGN KEY(CountryID) REFERENCES country(CountryID), CONSTRAINT ck_user_userName UNIQUE(userName), CONSTRAINT ck_user_userEmail UNIQUE(Email), INDEX(userName), INDEX(FirstName), INDEX(LastName), INDEX(IsActivated) )ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 ok thanks bahaa, took me a while to understand as I am not using InnoDB. In any case, I will try completing my script and I will be back here with the results. thanks guys. Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 ok thanks bahaa, took me a while to understand as I am not using InnoDB. In any case, I will try completing my script and I will be back here with the results. thanks guys. I use the InnoDB because I want to use the Delete on cascade. In my Isam you cant use delete on cascade Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 i did some research on cacade, i found the advantages to be very interesting. Link to comment Share on other sites More sharing options...
HartleySan Posted April 12, 2012 Share Posted April 12, 2012 Well, it seems like you guys have more or less resolved all the kinks, so I'll leave it at that, but just to clarify: bahaa's method is perfectly acceptable, but if you go that route, you will not get an entire history of modifications. You will only have the person who added a record and the person who last edited that record. If that's enough, then going with bahaa's method is best. However, if you want a full list of modifications (which is what I assumed), then you need a separate table/set of tables. You could have one table for all the mods, but you're going to end up with a lot of null values for the FKs, which is not ideal and is why I suggested a separate mods table for each normal table. As a final point, as bahaa mentioned, if you go with my method, there doesn't need to be any admin information in the clients table. Link to comment Share on other sites More sharing options...
bahaa Posted April 12, 2012 Share Posted April 12, 2012 Well, it seems like you guys have more or less resolved all the kinks, so I'll leave it at that, but just to clarify: bahaa's method is perfectly acceptable, but if you go that route, you will not get an entire history of modifications. You will only have the person who added a record and the person who last edited that record. If that's enough, then going with bahaa's method is best. However, if you want a full list of modifications (which is what I assumed), then you need a separate table/set of tables. You could have one table for all the mods, but you're going to end up with a lot of null values for the FKs, which is not ideal and is why I suggested a separate mods table for each normal table. As a final point, as bahaa mentioned, if you go with my method, there doesn't need to be any admin information in the clients table. What modifications do you can get other than who created the record, updated , what date was created and what date it was updated? I can think of the IP address for the one who made the changes but what other things?? If I was yo go your route I would only make one table not a mod table for each table. What if you have a really larg database like a thousand table, would you make another thousand table to track each table? I think there is no problem with having nullable Fks in your tracking table since(what I think) when fetching the table the nullable fields are skipped so it won't have any effect on the performance. Link to comment Share on other sites More sharing options...
HartleySan Posted April 12, 2012 Share Posted April 12, 2012 bahaa, it's not a matter of what type of mods you can make, it's a matter of the quantity. All I was saying was that if you put created_by and last_modified in the same table as the other data, you get only that, the person who added the record and the person who last modified it. However, for any number of reasons, you may want/need an entire history of changes made to each record. Again, I'm not saying that you need to do that, but by the way Mark described things, I assumed that's what he wanted. Also, while lots of null values would probably not greatly affect the performance of the database, my preference would still be towards a metadata table for each actual table that contains editable records. All the same, you could automate the whole process as well, which would eliminate the burden of maintaining so many extra tables. For example, check if a metadata table exists, and if it doesn't, create it. Problem solved. Link to comment Share on other sites More sharing options...
bahaa Posted April 12, 2012 Share Posted April 12, 2012 However, for any number of reasons, you may want/need an entire history of changes made to each record. History like what ? what kind of history can you get other than who made then changes and wehn ? Link to comment Share on other sites More sharing options...
HartleySan Posted April 12, 2012 Share Posted April 12, 2012 Like I said, it's not the type of changes that I'm debating, it's the quantity. With your method (as far as I can tell, but maybe I'm misunderstanding something), you only get a record of the creation of the record and the last modification. But let's say that a particular record has been 10 times and by 10 different people, you'd lose all the other modifications information. If you want to retain all that history information efficiently, then you'd need at least one other table, as opposed to including the mods history in the clients table. That's all I was saying. Link to comment Share on other sites More sharing options...
bahaa Posted April 12, 2012 Share Posted April 12, 2012 Like I said, it's not the type of changes that I'm debating, it's the quantity. With your method (as far as I can tell, but maybe I'm misunderstanding something), you only get a record of the creation of the record and the last modification. But let's say that a particular record has been 10 times and by 10 different people, you'd lose all the other modifications information. If you want to retain all that history information efficiently, then you'd need at least one other table, as opposed to including the mods history in the clients table. That's all I was saying. I know you can add as many as fields to track in a seprate table but I was just curious about the things you want to track. In such site I would not track such things, If i was handling a sensitiv data like banking information and confidential infromation for patient, then I would track who created the record ,when, who edited it, when was edited, who viewed a record and such things. Link to comment Share on other sites More sharing options...
HartleySan Posted April 12, 2012 Share Posted April 12, 2012 I wasn't saying I would do such a thing, I was simply offering a solution for the original poster, Mark. To be honest, the level of history tracking I recommended is completely unwarranted in all but the most security-conscious applications. Anyway, I think we understand each other, bahaa, but are just arguing semantics at this point. I'm not saying my way is better; simply that there's a difference in implementations, and the pros and cons of all such methods should be considered. Thanks for the interesting discussion. Link to comment Share on other sites More sharing options...
markifornia Posted April 15, 2012 Author Share Posted April 15, 2012 My web application is going along smoothly thanks to both of you. I am workin on webe site and in my database design i added those fields to my tables CreationDate, LastEdited, CreatedBy and LastEditedBy.... the createdBy and LastEditedBy are fk to the user table. When an admin make a changes to a record in a table, I use the userID in the session to add the userID to those fields for example, if the admin Bahaa make a changes to x record in the category table I add the userID for bahaa in the LastEditedBy fields. If you still don't understand any point let me know and I will be happy to help you. Bahaa, When updating the table containing CreationDate and CreatedBy these values will remain the same. How do you make a query update if they will have the same values. CreationDate and CreatedBy will not change during an query update, therefore are these fields skipped? Thanks, Mark Link to comment Share on other sites More sharing options...
HartleySan Posted April 15, 2012 Share Posted April 15, 2012 To update an existing row, you need to use the SQL UPDATE clause. In the clause, only specify the columns you want to update. Edit: Looks like this was elusive post #666. 1 Link to comment Share on other sites More sharing options...
markifornia Posted April 15, 2012 Author Share Posted April 15, 2012 Excellent! thanks! Link to comment Share on other sites More sharing options...
bahaa Posted April 15, 2012 Share Posted April 15, 2012 My web application is going along smoothly thanks to both of you. Bahaa, When updating the table containing CreationDate and CreatedBy these values will remain the same. How do you make a query update if they will have the same values. CreationDate and CreatedBy will not change during an query update, therefore are these fields skipped? Thanks, Mark When you do an update query, you only include the one you want to update.... You don't incclude all fields in your table every table you do a select or update query.... choose only the one you need to do select on or update. Link to comment Share on other sites More sharing options...
Recommended Posts