Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Database Design

  1. #1
    Join Date
    Feb 2007
    Posts
    10

    Smile Unanswered: Database Design

    Hello,

    I'm new to data modeling and even using forums, so please forgive any idiotic questions (yeah, I know, there's no such thing).

    Anyway, I was wondering that if I upload the design I came up with, if someone would look it over and tell me if I'm heading in the right direction?

    The database is for a CRM/Contact Management database and as I said, I'm new to this. I volunteered to do this for my boss not realizing how difficult it would be and now want to have the db at least 90% correct before I turn it in to him?

    So, if anyone would be willing to look over an Access 2000 file and tell me what they think, I'd be very grateful.


    Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If I get time over the w/e I can have a go at answering any questions for you.

    Just to point out that nobody is here to do your work for you - just help you out.

    So ask questions before seeking answers!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    10
    georgev,

    Thanks for taking the time to reply to my post.

    I'm not looking for anyone to do the work. I have a design already and the design program has the option to export it as an .gif and/or .jpeg file, so I was thinking I would just upload the picture of the design (assuming I can upload files on this website).

    I just wanted someone to tell me what they think of it. In other words, am I headed in the right direction and so forth...maybe point out the mistakes I've made.

    I've been working on this for about 10 days and have many of times without sleep, so a "fresh set of eyes" would be helpful.

    So, can you tell me how/where to upload a picture of the design (assuming I can), and if not, give me a suggestion on how others can see this since I do not have a website to put it on?


    Thanks!
    Last edited by bigdummy; 02-23-07 at 14:33.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    When you post, there is an option to attach a file to the post ... There is a limit to the file size tho.
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2007
    Posts
    10
    I will post the picture within the next hour or two.

    Sorry, but I have a meeting, then lunch, and then need to add a few relationships to the model before I generate the picture of it.

    Thanks.

  6. #6
    Join Date
    Feb 2007
    Posts
    10

    Smile Try this image

    Okay, back from meeting and lunch.

    Here's the image of the data model and please remember, I didn't put a comment on every relationship.

    Any comments/suggestions, even criticisms are more than welcome.


    Thanks.
    Attached Thumbnails Attached Thumbnails CRM.gif  
    Last edited by bigdummy; 02-23-07 at 16:30. Reason: Bad image-some things were cut off

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Table: Countries
    Do you need a CountryID if you have a CountryCode?
    It is ok to have a textual primary key!

    Unsure about the relationship between Employees and Regions...

    I think it should be something along the lines of:
    Many Employees can belong to one Organisation
    Many Organisations can be in one Region
    --unsure about difference between regions and states--
    Many Regions can be part of one State
    Many States can be part of one Country.

    I'm only thinking that becausse your relationshsips almost (well, they do) loop back to one and other - rather than being more linear.

    Someone pelase correct me if I'm wrong.

    (Please don't take what I have put as true, just what I can make out of your relationships (I don't have as indepth an understanding of theproject as you, so prove me wrong! ))

    - GeorgeV
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2007
    Posts
    10

    Smile

    GeorgeV,

    I should have put in some of the details of the project when I made the post, sorry.

    Some details and some of the constraints:

    - This CRM is not trying to be a "catch-all" for the whole dept. and the products we deal with. Actually, we plan on creating another, similar CRM for a different product/service the dept. deals with. So obviously, it could be a lot different, and better, but I figured I'd start with something a little less complex.

    Anyway, this CRM will have the dept. employees as well as field reps, who have direct (meet in person) contact with the customers as well as take their calls in their field offices. My dept. is part of a telecenter. So, field reps will have contact with the customers, but unfortunately, they won't be keeping data about this in this database.

    Each field rep has his own region. In other words, several regions, which have several states, and one field rep handles one region.

    - Many employees do belong to one organization.
    - Many organizations are in one region.
    - No. Many regions cannot be a part of one state. Many states can however be a part of one region.
    - And obviously, many states can be a part of one country.

    Hopefully, this helped with understanding what I'm after.

    I do not understand what you mean when you say "your relationships almost (well, they do) loop back to one and other - rather than being more linear." Please explain more.

    Thanks.

    bigdummy

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    At the loop comment - your relationships are in a big circle: employees > contacts > orgs > states > regions & back to employees.

    I feel that this has the potential to cause major problems in development of your system. I advise a more linear approach, perhaps having employees as (one of) the main parent table(s).

    But I'm sure one of the communit experts will say different!
    I just feel this will be inneficient and if you ever come to redesign the system, it will cause bigger issues.
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2007
    Posts
    10

    Some more details about the approach I'm using.

    georgev,

    Obviously, I'm not a database person, so forgive me if I don't get what you're saying right away.

    Here's some more of why I designed it this way.

    - Different (multiple) employees can take and/or receive calls from multiple contacts.

    - Most organizations have only one contact, but all organizations should have the ability to have more than one. So, multiple contacts can take and/or make calls to/from multiple employees in my dept.

    - Most organizations have just one location, but some have satellite locations. Any satellite locations will always be in the same state/province and country as the main location.

    - The state, countries, and region table are the way they are because I pulled in (imported) these tables from a country_state.mdb file I downloaded off the Web (forgot where exactly). Some of our customers are located in Canada, and a few outside North America.

    For the regions part, each field rep needs to have the states/provinces and country of his region listed and listed as abbreviations (i.e., NY for New York, PA for Pennsylvania, etc. and also the country listed this way...UK for United Kingdom...you get the idea), so it seemed necessary to have this field (that's what Country Code means...I believe in your previous post, you may have thought this was a primary key).

    Hopefully, this helped explain why I did things the way I did. If not, sorry, but as I said, I am ignorant about db's and how to make the relationships.


    bigdummy

  11. #11
    Join Date
    Feb 2007
    Posts
    10

    Updated image

    GeorgeV,

    I also updated the image, I had a couple of fields that were not needed, etc.
    Attached Thumbnails Attached Thumbnails CRM.gif  

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest you use country code as the primary key
    They're all unique, right? there's no point in using a unique identifier for something that is already unique!

    Perhaps the same can be applied with EmployeeType and EmployeeTypeID - is EmployeeType not always going to be unique?

    I think you have got your head round the way the relationships should be - As it stands, this database should work - we're just trying to optomize it/normalize it to make it more efficient. Don't take everything I say as fact, because it aint! Other people will give you other answers


    Oh just noticed that your Emails table is linked to Employeees and contacts - without either table having the foreign key.

    Also, it may be an idea to combine the E-mail table and the Calls table as so:
    InstanceID
    ContactID
    EmployeeID
    EmailYN <- Yes no field - no indicates a phone call.
    DateTime
    Subject
    Comments
    Welnitiated? Unsure what this is
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2007
    Posts
    10

    database design

    GeorgeV,

    - I understand what you're saying about the country code. I just had it this way since, as I said, I just used these tables from a different database and imported them. I will change them and re-import them into db.

    - Yes, employee type is NOT always going to be unique. In other words, a "telecenter associate" employee, a "field rep", and a "dept. manager" are some of the employee types. Obviously, there's more than one who is of type field rep and also more than one who is of type telecenter assoc.

    - That's why I'm here, to find ways to optimize/normalize since I want to try to prevent as many problems as possible before putting any data into it.

    - As far as the Emails table having a foreign key in the employees and contacts tables, I'm confused as to what you mean. These two tables have foreign keys in the Emails table since people have two ways (at least as far as this db is concerned) to communicate, either by phone or by email. Each time someone contact someone else, they're going to use of one the two ways, not both.

    If you remember from an earlier post...when field reps meet customers in person, they're not going to put this in the database.

    - Edit: I will also combine the calls and emails tables, but can you still explain about the foreign key thing I referred to in the previous 2 paragraphs, please. Also, the WeInitiated is a yes/no field just so we can tell if us (my company) or them (customer) initiated the call/email.


    thanks
    Last edited by bigdummy; 02-26-07 at 14:04.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    @ The foreign key thing - I was being a lemon - so ignore me

    And I understand what the EmployeeType is now! Like a job title, yes?
    And yeah, I can completely see why it's not the primary key.

    Why does the contacts table need the EmployeeID in it?

    You seem to have a very good grasp of normalization as far as I can tell.
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2007
    Posts
    10
    GeorgeV,

    First off, I attached the country_state.mdb file, so you can see what I had to work with. I don't know why designer/creator of it didn't use country_code as the primary key.

    Also, I attached the new, updated image with the changes.

    Thanks
    Attached Thumbnails Attached Thumbnails CRM.gif  
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •