Jump to content
Larry Ullman's Book Forums

Recommended Posts

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.

 

 

 

tool.jpeg

 

 

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

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.

  • Upvote 2
Link to comment
Share on other sites

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

  • Upvote 1
Link to comment
Share on other sites

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

  • Upvote 2
Link to comment
Share on other sites

tool1.jpeg

 

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

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

  • Upvote 1
Link to comment
Share on other sites

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

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.

  • Upvote 1
Link to comment
Share on other sites

@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

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.

  • Upvote 1
Link to comment
Share on other sites

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

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.

  • Upvote 1
Link to comment
Share on other sites

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

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

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

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

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

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

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

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

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

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

 Share

×
×
  • Create New...