Jump to content
Larry Ullman's Book Forums

Recommended Posts

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

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

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

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

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

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

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

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

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

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

 Share

×
×
  • Create New...