Tables and more tables design...
-
Hi!
I'm currently designing a database to be used for project control. For example, an employee will add a project and thereafter add tasks to the project. Basicly, the program is ment to be just like microsoft project, but simpler to use. Now to my problems!
Since I'm kind of new to "real" database design I've got some questions regarding the design.
Now all employees, customers and suppliers are in a super table called 'contact'. To this I have created another table, 'contactType' which is related to the contacts table through a one-to-many relation. This hold the three possible contact types, employee/customer/supplier.
BUT the contact table now stores entries that hold many null values, for example, a contact might be a person or a company. And since I will store alot of persons, many of my columns, for example VAT No and organisation no, will hold no information. = not good at all!
I have created a table for addresses, and another for phone/fax/mail and these are related to the 'contact' table through a many-to-many relation.
But how to take care of the fact that a customer can be a person OR a company?!?
I really would like to have a table, 'Person' which only holds information about a specific person.
Should I create another table 'Company' aswell and there store information about the company?!?
Both these should as I see it, be "sub-classes" of the 'contact' table...
Second question: How should I do to draw a relation from my 'project' table, which have a column called projectowner to the contacts table, which hold information about the employee who is the owner.
I'll guess you think I'm a totally retard, but if you never ask...
Best wishes
/Stefan Johansson
Best Wishes,
Stefan Johansson -
Stefan, sounds like your on the right path, so you're definately not a retard
Create a Person table, and a Company table. That way you've got only applicable columns in each.
There should be relationships between:
Company <-> Person
Company <-> Address
Person <-> Address
I'm guessing ahead, but you'll probably want to have many People related to a project, one-or-more Company related to a project, but only one projectowner which could be a Company or a Person.
In that case, create many-to-one relationship from Project to Company, and a many-to-one relationship to Person. (That is two columns in the Project table). Then create a Constraint on the Project table that Company IS NULL OR Person IS NULL that way only one of the two can be filled.
Julian Kuiters
juliankuiters.id.au -
Hi Julian!
I really, really, really appreciate the help!
Yes!, I think you understand exactly what I'm trying to accomplish! I like the guessing ahead!
Ok, I remove my 'Contact' table and create 'Person' and 'Company' instead. Thereafter I relate them as you described. Got it!
Two more questions, if you have time:
1. To the projects, it's possible to add tasks and therefore I've created a table 'Task'. But how do I do to be able to add sub-tasks to a task?!? Should I just create a one-to-many relation from the 'Task' table to the 'Task' table?
2. Do you have any books to recommend on database design?!?, I'm not interested in adminstering/installing, just pure design!
Can't tell how much this help means. You've saved me alot of time! ...and a lot of headache
.
Best Wishes,
Stefan Johansson -
1. a one-to-many relation Task <-> Task will work
2. umm ... I think i learnt all i know about database design from the thousands of online articles and SQL Server help pages that I've read. So though I can't recommend any books, check amazon or somewhere for reader feedback on "relational database design" type books. Most of them should be database server independant, but try to get books based on Sql Server.
Other than that, participating in these forums regularly helps test yourself. Answer people as often as you can, even if you're not 100% sure. Someone will let you know if there's a better answer, and you can improve that way.
Julian Kuiters
juliankuiters.id.au -
Regarding sub tasks...a possibility is to keep a parent task key in each task record. Any task with a null parent task key is the parent task. All other tasks point to the parent or to a child of a parent or....ad infinitum. Then you can traverse and collect all sub tasks of each task.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
Task<-->Task is refered to as a "Self Join" if you want to look into it further look into "Hierarchies" as well.
-Isaiah -
Hello again!
G Bryant: That's a good tip!
iadams: I will search the forums for information about "Self Join" and "Hierarchies" as proposed.
Thanks for the help everyone! it's deeply appreciated!
Best Wishes,
Stefan Johansson -
Hi!
Finally, after many hours of frustration, I think I've finished the design of my first ER-database
Without your help, lots of excellent guides and this forum, it probably wouldn't have been possible for me to learn so much in such short time. Thanks!
My last(?!?) two questions regarding this:
1. Does any of you have time to take a quick look at my design and "talk me to right" if something is totally messed up?!? My biggest concern is the relation between 'Task' and 'PreviousTask'. The table 'PreviousTask' is ment to store information about relations between tasks.
For example: A task, is a subtask of another task and shouldn't be able to set its status to 'Finished' if not all previous tasks are finished. The 'PreviousTask' table is ment to store this relation.
2. Is my 'Verb phrases', and 'Inverse phrases' correct?
http://www.mtek.chalmers.se/~johansss/Resman_v3.pdf
Many thanks!
Best Wishes,
Stefan Johansson
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply