Good afternoon guys, i need some help with my database design, i made a webpage because i thought it would be easier, i had to include a picture. anyway this isn't a scam to get people to ckick on my webpage, i sincerely need the help. here's the page
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Help with relationships and normalization
- Thread starter gmagerr
- Start date
It's really better not to post your details off the forum. If all the information is here, it becomes part of the site for good, and other folks can benefit from the question and answer in the future.
For the benefit of future readers, gmagerr's question was: "Ok i'm in the middle of writing a web application in ASP. i've designed a database, but want to know how i can make it better. Like for instance normalizing it, i want to be able to delete something from the jobs table and have all the other tables delete the information linked to that job. and anything else you might see or think of that will help me."
The offsite page showed a Relationships window with a bunch of tables with one-to-many relationships. In particular there is a Jobs table with key JobID and non-key column JobCode, and an OrderDetails table with key OrdersID and column JobCode. These two tables are related on JobCode. There are numerous other tables which are related via the parent table's primary keys.
OrderDetails is related to Jobs by JobCode, which is not the primary key of Jobs. I'm going to assume that in the Jobs table you have a unique index on JobCode, which would make this a one-to-many relationship.
As far as I can tell from the diagram, everything seems to be well normalized. The only question I have is with the relationship between Employees and Jobs, which appears to be one-to-many. Is it true that there can be only one Employee per job? Maybe so, if the relationship represents some kind of supervisor, or if the nature of the business is such that only one employee is assigned.
You said you "want to be able to delete something from the jobs table and have all the other tables delete the information linked to that job." Are you sure you want to delete data from all the other tables? I can see having the OrderDetails deleted, but why would you want to delete Customers, Employees, and Products, for example? These things would persist after the job was finished, it seems to me.
To get the OrderDetails to be deleted automatically, you need to set up relational integrity on its relationship to Jobs, and include the Cascade Deletes option. The diagram indicates that you chose not to include relational integrity when you created the relationship (it lacks the "1" and "infinity" symbols on the ends of the join line). To add relational integrity now, just double-click the join line in the Relationships window and click the check box for Enforce Relational Integrity, then click for Cascade Deletes. This causes data in the child table (OrderDetails) to be deleted when you delete the related data in the parent table (Jobs).
You might want to do this with some of the other relationships as well. For instance, you could do this with Customers, so that when you delete a customer you delete all the job history right along with it. But then, you might not want to do this, either. It probably doesn't make sense to delete a customer who has jobs in progress. If you don't allow Cascade Deletes, then an accidental attempt to delete the customer will be aborted by Access, which will save you from making the mistake.
I have one other comment on your design. If JobCode has a unique index, which I have assumed, then why didn't you make it the primary key? Or alternatively, why not base the relationship with OrderDetails on the primary key, JobID? It will certainly work the way you have it, though basing a relationship on something other than the primary key (but still something that has a unique index) can occasionally force you to make queries more complex than necessary.
On the other hand, maybe JobCode doesn't have a unique index, in which case the relationship is many-to-many (shown as "Indeterminate" in the Join Properties dialog). Such a relationship is useful as documentation in the Relationship window, but usually isn't very helpful when building queries. The queries will come out non-updatable, for one thing. If this relationship truly is many-to-many, you should probably interpose another table, called perhaps JobOrderDetails, that includes JobID and OrdersID. Such a table is called an "associative table", and it's used to transform a many-to-many relationship into a pair of one-to-many relationships. In general, it's a good thing and an indication of proper normalization to have your database contain only one-to-many and occasional one-to-one relationships. Rick Sprague
For the benefit of future readers, gmagerr's question was: "Ok i'm in the middle of writing a web application in ASP. i've designed a database, but want to know how i can make it better. Like for instance normalizing it, i want to be able to delete something from the jobs table and have all the other tables delete the information linked to that job. and anything else you might see or think of that will help me."
The offsite page showed a Relationships window with a bunch of tables with one-to-many relationships. In particular there is a Jobs table with key JobID and non-key column JobCode, and an OrderDetails table with key OrdersID and column JobCode. These two tables are related on JobCode. There are numerous other tables which are related via the parent table's primary keys.
OrderDetails is related to Jobs by JobCode, which is not the primary key of Jobs. I'm going to assume that in the Jobs table you have a unique index on JobCode, which would make this a one-to-many relationship.
As far as I can tell from the diagram, everything seems to be well normalized. The only question I have is with the relationship between Employees and Jobs, which appears to be one-to-many. Is it true that there can be only one Employee per job? Maybe so, if the relationship represents some kind of supervisor, or if the nature of the business is such that only one employee is assigned.
You said you "want to be able to delete something from the jobs table and have all the other tables delete the information linked to that job." Are you sure you want to delete data from all the other tables? I can see having the OrderDetails deleted, but why would you want to delete Customers, Employees, and Products, for example? These things would persist after the job was finished, it seems to me.
To get the OrderDetails to be deleted automatically, you need to set up relational integrity on its relationship to Jobs, and include the Cascade Deletes option. The diagram indicates that you chose not to include relational integrity when you created the relationship (it lacks the "1" and "infinity" symbols on the ends of the join line). To add relational integrity now, just double-click the join line in the Relationships window and click the check box for Enforce Relational Integrity, then click for Cascade Deletes. This causes data in the child table (OrderDetails) to be deleted when you delete the related data in the parent table (Jobs).
You might want to do this with some of the other relationships as well. For instance, you could do this with Customers, so that when you delete a customer you delete all the job history right along with it. But then, you might not want to do this, either. It probably doesn't make sense to delete a customer who has jobs in progress. If you don't allow Cascade Deletes, then an accidental attempt to delete the customer will be aborted by Access, which will save you from making the mistake.
I have one other comment on your design. If JobCode has a unique index, which I have assumed, then why didn't you make it the primary key? Or alternatively, why not base the relationship with OrderDetails on the primary key, JobID? It will certainly work the way you have it, though basing a relationship on something other than the primary key (but still something that has a unique index) can occasionally force you to make queries more complex than necessary.
On the other hand, maybe JobCode doesn't have a unique index, in which case the relationship is many-to-many (shown as "Indeterminate" in the Join Properties dialog). Such a relationship is useful as documentation in the Relationship window, but usually isn't very helpful when building queries. The queries will come out non-updatable, for one thing. If this relationship truly is many-to-many, you should probably interpose another table, called perhaps JobOrderDetails, that includes JobID and OrdersID. Such a table is called an "associative table", and it's used to transform a many-to-many relationship into a pair of one-to-many relationships. In general, it's a good thing and an indication of proper normalization to have your database contain only one-to-many and occasional one-to-one relationships. Rick Sprague
- Thread starter
- #3
thanks for the reply rick, the reason i wanted to delete the customers associated with the jobs is because (and i don't know how to fix this yet) i'm adding duplicate customers in the database. i have a from that you fill out with the jobsite info, the customer info and the job info, this one form inserts into the three tables. if i can figure a way to have it check to see if the customer already exists, then i think i'll be in better shape. i changed the joborderdetails so the jobs relationship is one to many (thanks) and did the cascade delete. if there's anything else you want to suggest, please do so. again thanks.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question