markifornia Posted April 11, 2012 Share Posted April 11, 2012 I am not sure if this would be better suited in another forum, but since I am working through this book and understanding the normalization process/ database design, I went ahead. I am working on a client management tool to organize some of my clients. The database flowchart below is a rough sketch, it may or may not fullfill the normalization process which is expected. I am still premature in this area (the normalization process), so bear with me. See below a database diagram I have created for a client management tool that will hold a table for clients. The clients table will have multiple locations, multiple websites, and multiple images. Based on the example given on chapter 7, (I believe, its chapter 7 where it talks about the normalization process. My apologies, my book actually wrapped in half, and half of it is somewhere.) it is the movie- actors example. You'll see I have created: client_location, client_website, and client_image I am unsure if these tables are necessary intermediaries (at the moment I'm still learning normalization). However, some advise would help me understand why they are necessary (or not?) Based on my understanding the "client" table is also an intermediary. So if I understand correctly, there are two intermediary tables? Is this okay to do? Thanks appreciate it Mark Link to comment Share on other sites More sharing options...
HartleySan Posted April 11, 2012 Share Posted April 11, 2012 Hey, Mark, glad to see that you're so interested in DB design and making such a candid effort. Reading your post, I'm having some trouble understanding the connection between what you described you want and your actual UML chart. Looking at your UML chart, it seems like you have a lot of INT values that are IDs. I'm wondering where all the actual data goes. Given what you described you want, I might create a DB structure like the following: clients table - client_id - client_name - (Any other relevant client info that doesn't belong in a separate table) (I don't know what the cl_id is, but it seems like a repeat of the client_id. Also, are clients allowed to be admins on your site? I'm a bit confused about this.) client_locations - location_id - client_id (Foreign key from the clients table) - address (I'm assuming that this table is for the physical addresses of clients, but I could be wrong. See Larry's discussion on handling addresses to decide how to break down (or not break down) the address field.) client_websites - website_id - client_id (Foreign key from the clients table) - website_url client_images - image_id - client_id (Foreign key from the clients table) - image_url (I'm assuming that this table is for images a client displays on their website or their page on your website. Again, I'm a little confused about what kind of site you're building in the first place.) Basically, it seems like your clients table should have a one-to-many relation with the other three tables since a client could potentially have multiple addresses, websites or images. Lemme know what you think. Thanks. 2 Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 Why did you put the cl_id in the client table? You break the normalization when you store the full name in one filed, if you store the full name in one field you want be able to do any sotring on the names. you should make one filed for first name and another one for last name I think you only need 5 tables 1 Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 I will give you my solution in few minutes Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 admin AdminID FirstName LastName Password client ClientID FirstName LastName location LocationID Address ClientID FK to the client table website WebsiteID Url ClientID Fk to the client table image ImageID Image ClientID FK to the Client ID 2 Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 Please see revised diagram above. Thanks you to you both, I have taken your advice into consideration during my revision. @Hartley - I should have been a lot clearer with what I am trying to construct. I am building a management tool to organize my clients. Lets say I have a client named "Blizzard Inc.". Blizzard would have multiple locations throughout the United States, multiple websites (starcraft II website, diablo website, and world of warcraft website are their popular games), and then some images to represent each client - perhaps a blizzard logo and some nice character art work. Also the cl, cw, and ci are just short for client_location_id, client_website_id, client_image_id. Those letters were confusing, I should have spelled it all out. In any case, the diagram is now revised. That being said, I was confused as to why on chapter 9 there is a movie-actors table inbetween the movie table and the actors table. That is why on my first chart, I have so many tables. @bahaa - Thank you for the additional comments to add to Hartley's suggestion as well, your advice is very similar. The cl_id in the clients table was an error and should not have been there. It looks like I'm getting close to getting this diagram to work before I start populating and testing queries. I did notice in both your comments there is something missing, maybe I missed this important piece of information: Why isn't the admin_id a foreign key to the clients table. I do have to mention though, that there will be multiple clients (blizzard inc, rockstar games, sony online entertainment, trion worlds etc). I would like to know which admin created and/or modified each table. Should the admin_id be a foriegn id to each and every table now? Thanks again. Mark Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 You cam add auditing fields to the other tables and you can add more than one field to each table example of fields: CreationDate CreatedBy LastEdited LastEditedBy The client table did not have a contact name like first name and last name. I noticed you use varchar(255) for all of your varchar fields, you should only use the size you need , for example you won't need 255 for a city name or first name. for the city name I don't think you would need more than varchar(30), first name maybe 55. for the location, if you want to have more than one address for the same person then you to make a bridge table between the client table and the adress table. You also can break the location table into these tables: country and city 1 Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 That being said, I was confused as to why on chapter 9 there is a movie-actors table inbetween the movie table and the actors table. That is why on my first chart, I have so many tables. I don't remember the movie tables and I did not read the whole book but I think the table in between is because the many to many relation ship in movies case. An actor can have many movies and a movie can have many actors. Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 I like the auditing fields idea and will add. Is it correct to have the admin_id in the client table ? Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 CreationDate CreatedBy LastEdited LastEditedBy Should I add admin_id to the location, website, and image tables as well? How would I be able to know auditing information from these tables? Link to comment Share on other sites More sharing options...
HartleySan Posted April 11, 2012 Share Posted April 11, 2012 bahaa is right. The reason for the in-between table in the actors/movies example is because there is a many-to-many relationship between the two (i.e., one actor is in many movies, and one movie has many actors). All many-to-many relationships should always be broken up into one-to-many relationships, thus the interim table. If that still doesn't make sense, ask, and I'll try to explain it more clearly. I still don't understand the purpose of the admin table. Are people that work for your clients also admins on your site? Sorry, but as far as I can see, the admin table is pointless. Furthermore, while this is optional, if each website will only have one image associated with it, you can combine the websites and images tables. Furthermore, if each location has one (and only one) website, you could combine the websites and images tables into the locations table, thus making one table out of three. Of course, to do this, you must ensure that there is always a one-to-one relation between these three types of data, which, by the way you described things, seems to be the case. So, in the end, while I am still not 100% clear on your intentions, a clients table and a locations table with the client_id as the foreign key seems more than sufficient for your needs. While it's generally better to overly divide up relations (as opposed to "underly" dividing up relations), more tables can slow things down, so why use three tables when one will do the job, right? Well, lemme know if you have any other questions. 1 Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 @hartley - thanks for clarifying the movies-actors table, as it was a very unique example in the book. It was not clear to me it was a many-to-many relationship. But if the movies-actors table is many-to-many as shown in the book, then the wouldn't you say the the database design here is incorrect? That it should be converted into one-to-many? The admin table is really just several select users that can edit ALL the tables. Lets say you, me and bahaa are managing this tool and want to edit the clients, location, website and image table. We would all be admins, and I would like to know who added or updated each table. I would think that an admin table is required for this, should it not? I will have multiple images, locations and websites for one client. But I do understand your point, that I can combine them given that there will be only one image, website and location per client. Hartley I do have a question still regarding how I can find out which table was modified, added or deleted by an admin user. I have explained why the admin table exists earlier. I hope you understand its purpose. Let me clarify that clients will not be touching this table nor its front end interface, but administrators will be constantly adding new records to these tables. I hope this is clear. So in order to find out who edited each table, I would need admin users created. Let's say I have an admin_id=1, bahhaa admin_2, you have admin_id=3. When any records are added to any table, I would like to know who worked on it??? Link to comment Share on other sites More sharing options...
HartleySan Posted April 11, 2012 Share Posted April 11, 2012 Okay, I better understand now. In that case, you would need an admins table, which is essentially just a table of users that can access the system, right? If you want to record the changes being made, then you will need to create separate tables to manage all that for you. For example, if someone adds a client to the clients table, that client would be added to the clients table, and in addition, you'd need a separate table (say, client_mods) to record who made what kind of change when. How you manage this is totally up to you, of course. If you do this, you need to ensure that all the admins (sans you, of course) can only access and edit the data through the PHP interface you create. Don't allow them to edit data through a separate interface, like phpMyAdmin, for example. Anyway, I don't think your admins and clients tables need to be connected at all. Your admins table should interact with its own metadata tables, and then the other tables would just be. 1 Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 Sorry to drag this out, but I must say that most of my questions have been answered. I am not clear on what you mean by creating another table called client_mods. Which table(s) would this relate too? Wouldn't I have to add those fields(last_modified, date_added, created_by) to each and every table? admin admin_id admin_firstname admin_lastname admin_username admin_email Link to comment Share on other sites More sharing options...
HartleySan Posted April 11, 2012 Share Posted April 11, 2012 You can easily add a last modified field to a table like the clients table, but that's not going to provide you with a history of changes, nor will it provide you with who made those changes. My point is, you can't just tack some extra fields onto the existing tables and call it a day. You need to create completely new tables to track the entire history of changes made to the clients, locations, and websites tables, etc. Probably the most logical solution would be to have another table for each normal table, which keeps track of the modifications made to that table, which is where the example table client_mods came from (with "mods" being short for "modifications"). Anyway, I think you get the point. 1 Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 hi hartley, i get an idea of what you mean. I just wanted to get a visual of what how a client_mods table would relate to normal tables. For the sake of simplicity, one example is fine. If I understand correctly, see example below: admin table admin_id admin_firstname admin_lastname admin_username admin_email client table client_id admin_id client_company_name client_mod table client_id last_modified date_created location table location_id client_id location_street_address location_city location_state location_zip location_mod table location_id last_modified date_created Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 After some more thinking, I realized that when a table is updated by an admin user, upon update, I can create a query that can delete the existing user that initially created the record, and then insert the new admin user who has edited the record. In a way he will be the new owner of the record until someone else has updated it. I will have: added_by (this is created initially when the records is created) modified_by and last_modified (done through what i said above) Thoughts on this? Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 You can easily add a last modified field to a table like the clients table, but that's not going to provide you with a history of changes, nor will it provide you with who made those changes. My point is, you can't just tack some extra fields onto the existing tables and call it a day. You need to create completely new tables to track the entire history of changes made to the clients, locations, and websites tables, etc. Probably the most logical solution would be to have another table for each normal table, which keeps track of the modifications made to that table, which is where the example table client_mods came from (with "mods" being short for "modifications"). Anyway, I think you get the point. He can add those fileds to every table and get who made the changnes, I have them in my table and I manage to get such information. For example, in admin area I have a page called categories_manager that display the categorty information plus the creation date, modification date, created by and last eduted by for each record. Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 thanks bahaa, i thought it was possible. though through hartley's example there are alternatives. bahaa - do you have the user admin id foreign keys in those tables? Are they related? This would put the nail in the coffin as far as my initial question(s) are concerned. If not, then I'll have to find a solution thanks, Mark Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 hi hartley, i get an idea of what you mean. I just wanted to get a visual of what how a client_mods table would relate to normal tables. For the sake of simplicity, one example is fine. If I understand correctly, see example below: admin table admin_id admin_firstname admin_lastname admin_username admin_email client table client_id admin_id client_company_name client_mod table client_id last_modified date_created location table location_id client_id location_street_address location_city location_state location_zip location_mod table location_id last_modified date_created If you want to follow Hartley suggetion about making sperate table to track the changes, take off the admin_id from the clinet table because don't need it there. Second, the tracking table should be for all of other tables, you can't make a tracking table for each table. In the tracking table add the other tables ids. When an admin make a changes to the client table for example you add this record to the mod table the id for the client_id the date and the id for the admin and the ids for the other tables should be null Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 thanks bahaa, i thought it was possible. though through hartley's example there are alternatives. bahaa - do you have the user admin id foreign keys in those tables? Are they related? This would put the nail in the coffin as far as my initial question(s) are concerned. If not, then I'll have to find a solution thanks, Mark No, you should not add the admin_id to those table. the admin_id in the mod table should be a fk to the admin table Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 Ok thanks for clarifying Hartley's suggestion - I now understand what he was talking about. Here is how I understand Hartley's method below: mod table mod_id client_id (FK) location_id (FK) website_id (FK) image_id (FK) I would like to also understand your approach though. Explain your method - did you add at the admin user id to each table (the tables that contain the created by, modified by fields)? Link to comment Share on other sites More sharing options...
markifornia Posted April 11, 2012 Author Share Posted April 11, 2012 No, you should not add the admin_id to those table. the admin_id in the mod table should be a fk to the admin table I am talking about your method not Hartleys. When you have created_by, modified_by fields on tables, is the admin_id related to those tables? Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 Ok thanks for clarifying Hartley's suggestion - I now understand what he was talking about. Here is how I understand Hartley's method below: mod table mod_id client_id (FK) location_id (FK) website_id (FK) image_id (FK) I would like to also understand your approach though. Explain your method - did you add at the admin user id to each table (the tables that contain the created by, modified by fields)? 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. Link to comment Share on other sites More sharing options...
bahaa Posted April 11, 2012 Share Posted April 11, 2012 I am talking about your method not Hartleys. When you have created_by, modified_by fields on tables, is the admin_id related to those tables? Yes the admin_id is related to those tables but you don't add the admin_id in the table and instread you make those fields fk to the admin_id here is an example of my tables CREATE TABLE IF NOT EXISTS `category`( CategoryID INT UNSIGNED AUTO_INCREMENT, ParentID INT UNSIGNED, Title VARCHAR(120), Description TEXT NULL, Ordering INT UNSIGNED NOT NULL DEFAULT 1, Visible TINYINT DEFAULT 0, CreationDate DATETIME, CreatedBy INT UNSIGNED NOT NULL COMMENT 'Fk to the user table', LastEdited timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, LastEditedBy INT UNSIGNED COMMENT 'Fk to the user table', CONSTRAINT pk_category_CategoryID PRIMARY KEY(CategoryID), CONSTRAINT fk_user_category_CreatedBy FOREIGN KEY(CreatedBy) REFERENCES user(UserID), CONSTRAINT fk_user_category_LastEditedBy FOREIGN KEY(LastEditedBy) REFERENCES user(UserID), CONSTRAINT fk_category_ParentID FOREIGN KEY(ParentID) REFERENCES category(CategoryID) ON DELETE CASCADE, INDEX(Ordering), INDEX(Visible) )ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; Link to comment Share on other sites More sharing options...
Recommended Posts