Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    9

    Unanswered: Database Design Help

    Greetings, I am trying to teach myself Access and need some help and advice on designing a fairly simple contacts database. This isnt the complete outline of what I am trying to do but it has enough to answer the questions I need answered.. Sorry if it is confusing. I think I am having a little bit of trouble with relations and normalization concepts(and the fact that I don't know all the bells and whistles of accesses commands)..

    Ok this is what I'm going with to get started before I add all the other fields:

    *Key

    TABLE CONTACTS
    *ContactID
    FirstName
    MiddleName
    LastName
    PhoneNumber
    Email

    TABLE PHONENUMBERS
    *AreaCode
    *PhoneNumber

    TABLE EMAIL
    *Emails
    Subscribed


    I will add address, etc, later.

    These are my logical needs for the database:
    I need to be able to create a form that enters data directly into CONTACTS with the following needs for specific data.
    I need to be able to list Emails and PhoneNumbers by themselves without duplicates (normalization from what I understand). And import Emails into the database (with or without a contact reference, I don't need to know specifics on this yet though so please only pay attention to my questions on design so that this is possible) so that I have no Email duplicates. I have been able to create a relationship between EMAIL and CONTACTS where I can have a contact with an Email but only if the Email is already entered into EMAIL.Emails, Is there any way to set the settings to where I can enter an Email into CONTACTS.Email and have it automatically add itself to EMAIL.Emails if it's a new Email?
    Also, I have the same problem I am having with the Emails with the number having to exist in PHONENUMBERS first. And I need to be able to search my numbers by AreaCode. Is there a way to get both PHONENUMBERS.AreaCode and PHONENUMBERS.PhoneNumber to display in CONTACTS.PhoneNumber? I am able to get both to show up beside each other in the drop down menu box but only the first item shows up in the box after an entry is selected. Would there be a way to display data from multiple fields within a field (using format or input mask maybe)? And also the relationship wont let me enforce referential integrity which is probably a bad thing since I am relating one field to multiple other fields (which are all part of a single KEY in their table).
    Are the things I am trying to do even possible the way I am trying to do them? Or do I have to create code myself in the form where it will have to check the Keyed database first, then create a new item and/or reference the item into the contacts table?
    Also one more thing I just remembered.. I seem to be able to get a + under certain configurations in the Emails table to where if i enter an email into EMAIL.Emails I can then enter in CONTACTS data to go with it, yet I can't figure out how to do it vice versa where I can add a new member to Emails from contacts.
    Anyway I hope this all makes at least some sense, Any help or suggestions would be appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    having had a very cursory look, at first seems promising

    I don't understand what your EMAILS table is trying to represent.

    I think you need to think about the data stores before worrying about you display infomation. Bear n mind that you are designing the data storage and retrieval part of the app, the data presentation is done elsewhere.

    some minor thing to think about..
    whats your association between a person and a phone number - ie how does is phone number associated to an individual.

    If you are splitting off the area code then you possibly ought to conisder the international code

    a person may have more than one middlename
    a person may also prefer to have a title (eg Dr, Prof, Herr Ingineur und so weiter)
    a person may also have qualifications
    a person may have a preferred name (eg 'Pete' instead of 'Peter')
    - it may be that your app doesn't need to store that level of information.

    how do you propose to allow for a person to have multiple phone numbers (eg home, work, mobile etc...) or email addresses
    how do you propose to handle say a chnage of family phone number

    As regards you means of inputting information I think you should look at a form with 2 subforms (possibly on a tabbed dialog)

    HTH

  3. #3
    Join Date
    Nov 2005
    Posts
    9
    Quote Originally Posted by healdem
    having had a very cursory look, at first seems promising

    I don't understand what your EMAILS table is trying to represent.

    I think you need to think about the data stores before worrying about you display infomation. Bear n mind that you are designing the data storage and retrieval part of the app, the data presentation is done elsewhere.

    some minor thing to think about..
    whats your association between a person and a phone number - ie how does is phone number associated to an individual.

    If you are splitting off the area code then you possibly ought to conisder the international code

    a person may have more than one middlename
    a person may also prefer to have a title (eg Dr, Prof, Herr Ingineur und so weiter)
    a person may also have qualifications
    a person may have a preferred name (eg 'Pete' instead of 'Peter')
    - it may be that your app doesn't need to store that level of information.

    how do you propose to allow for a person to have multiple phone numbers (eg home, work, mobile etc...) or email addresses
    how do you propose to handle say a chnage of family phone number

    As regards you means of inputting information I think you should look at a form with 2 subforms (possibly on a tabbed dialog)

    HTH

    There are plenty more columns I will be adding such as the things you suggested, but the minimalist database with the dilemas I stated pretty much covers all the things I need to learn about in order to implement all the other columns.

    Basically the EMAILS table would be a newsletter like database, which keeps track of who is subscribed and who is not (possibly other stuff too depending on what I decide). I would be exporting and importing from the emails list and I need it to eliminate duplicates so that no one gets mailed twice. And it saves space if two people have the same email.

    Now as far as its relationship to contacts, lets say I have someone named Bob, with the email bobandmary@website.com and his wife Mary who has the same email, if I made Email part of contacts I would get a duplicate, unless i turned duplicates off, but in this case I WANT a duplicate in the contacts because I want both bob and mary to point to their email, I just want a list that has no duplicates where bob and marys contact email field can be pointing to the one email address in emails.

    same with phone numbers, lets say bob and mary have the same phone, I want it to point to the one phone number, and also if there are alternate phone numbers, lets say their home number is the same as their fax number (dumb example I know, but I may need something similar later on as far as the logic behind it), I would need 4 fields to be pointing to 1 field. that saves storing the number 4 times also.

    So.. I have been trying to mess with the forms a bit and figuring out a way to enter in a name and an email, but so that the email can be duplicate for the contact but not duplicate inside its table.. And I generally get an error stating that I must enter data into the email table first, how would I do that at the same time as making a new contact then linking the newly created email to that very same newly created contact..? I was able to create a form that almost seemed to be what I wanted.. Using wizard and bringing over ID, and Names from contacts, then bringing the emails from the email table.. but that wouldn't allow me to enter in data in any of the fields except for the email field... anyway... any more help would be appreciated.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suspect that you need to do a bit more investigation about normalisation. Have a look at rudy's site

    One of the key principles is that you don't duplicate data. Classically you would use a system generated number to act as a prime key and associate that number with the actual data.

    so for instance your phone number table could comprise
    PhoneID autonumber
    PhoneIntCode as string
    PhoneSTD string
    PhoneNo string
    PhoneExt string
    PhoneTypeID number (long)
    you could have an indicator which identifies if its a work or home

    you might have another table which classifies the type of number eg
    type 1: Phone
    type 2: Phone / Fax
    type 3: ISDN DATA
    type 4: Home Phone
    type 5: Alt Work Phone

    HTH

  5. #5
    Join Date
    Nov 2005
    Posts
    9

    hmmm

    Ok I'm begining to think the problem isn't in my design at all, and that I am trying to force the database to do something automatically that it isn't able to do, I think my problem is this concept right here:

    (fake database as an example to convey one specific point, ignore its flaws)
    TABLE1
    *Name
    Email

    TABLE2
    *Email

    Email columns are related to where TABLE1 can have the same email listed multiple times, but it is linked to only one instance of email in TABLE2.

    Ok, lets say I want to enter data into this now and I want it to look and act like this


    Name : [textbox]
    Email : [textbox]

    That is all I want my end user to see. I want it to, create a new row in TABLE1, and if there is no email then I can leave it blank, if there IS an email I want it to either create an instance of that email in TABLE2, or point to the Email that is already in TABLE2. (I can currently make it point to the Email already in TABLE2 but what do I do if I have an entirely new email?)


    to step away from the contacts concept and use something else:

    TABLE1
    *PizzaType
    Topping

    TABLE2
    *Topping

    Form:
    PizzaType : [textbox]
    Topping : [textbox]

    lets say I sell pizzas that only consist of 1 topping (ignore the logical flaws in the fact that there could be many toppings), Ok lets say I just got a shipment of new never seen before toppings (before, Topping consisted of Pepperoni, Hamburger, and Mushrooms). I want my cashier to be able to take the data off of a piece of paper "Thin Crust" and "Artichokes" and create a new order:

    Form:
    PizzaType : [Thin Crust]
    Topping : [Artichokes]

    and have it create a new unduplicated row in Topping with the data "Artichokes"


    Now I can see why conceptually this would often be avoided since you do not want a user to enter in Atichocks and Artachokes, it would defeat the whole purpose of keeping data integrity, but in the case of an Email, I need a central location for all emails without duplicates since I personally would be using the email table seperately from the end users program as well.

    Anyway.. I hope this makes sense.. thanks.

    (oh yeah I can take off Enforce Referential Integrity and get it to act almost like I want it to as well, only it DOESN'T create the member inside of Emails like I want it to, it just places a new email directly into TABLE1 and nothing into TABLE2..)

    **To note and scratch a potential answer, I can't go through the emails in TABLE2 to create a contact in TABLE1 because there may be other information in TABLE1 that would have similar problems with potential TABLE3+'s (for instance PhoneNumbers and Emails tables having the same functionality)**
    Last edited by mcyborg; 11-29-05 at 13:57.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lets take your example of bob and mary doe

    they may live together so have the same home address, it may even have its own phone number and fax number. Its arguable that an address may have / own a phone/fax etc. however its likely that an individual will have an email address in their own right. Indeed certain database purists claim that you should not use Nulls, insterad create a child table for that data.

    So the question for you as the data modeller is do you store 2 separate addresses entities or do you store a single address. Normalisation rules would suggest a single address. You would then link / assoicate the address with both bob and mary, referring to a system generated ID - lets be creative and call it 'AddressID'

    ie you would store the AddressID of their home address in their personal details. This AddressID identifies the address in the addresses table. if you are not sure, or I haven't been clear enough have a look at Paul Litwins toreatise on r937's site

    In practise however, you may decide for operational reasons to have a separate address for both - rules are only rules if you choose to obey them.

    HTH

  7. #7
    Join Date
    Nov 2005
    Posts
    9

    sigh

    If I have Emails column as a key for a table Emails, why do I even need a addressID?? Even if I had an address ID it brings about the same Dilema:

    Name [textbox]
    Email [textbox]

    if the email the user is entering isnt already in the database it will not create a new row in email.email and then link contacts.email to that specific email.email...........

    for example if

    contacts NAME , EMAIL
    row1 = bob , email.email row1
    row2 = bobswife, email.email row1

    email Email
    row1 = bob@bob.com

    then I open a form that will have 2 members, both pointing to the same email.

    1 of 2
    Name: [bob]
    Email: [bob@bob.com]

    2 of 2
    Name: [bobswife]
    Email: [bob@bob.com]

    Ok, lets say I go to create a new member

    I can do this just fine:

    Try1
    3 of 3
    Name: [bobsson]
    Email: [bob@bob.com]

    What I can't do but need to be able to do is:

    Try2
    3 of 3
    Name: [bobsson]
    Email: [bobson@bob.com]

    It will return an error stating that the email isnt in the email table. I need to know how to get Try2 to do what I want so that when I enter 3 of 3 my database looks like this:

    contacts NAME , EMAIL
    row1 = bob , email.email row1
    row2 = bobswife, email.email row1
    row3 = bobsson , email.email row2

    email Email
    row1 = bob@bob.com
    row2 = bobsson@bob.com

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you need to revist some of the documetnation n relational theory - its hard to get over in a forums, but the reference to Paul Litwins piece is well worth following up.

    often it said on data modelling concentrate on the key and only the key

    lets look at a a theoretical company

    it has an entity (table) called customer
    which comprises CustomerID (the key) and other rows such as customer name, address, tel etc...

    it has an entity called products
    whic comprises ProdID and other rows such as product descrpition, price case size etc....

    it has an entity called order
    which comprises OrderID and (unsurprisngly) other rows such as orderdate, deliverydate, CustomerID [so that you can identify who the customer is realting back to the Customer entity]

    it has an entity called orderDetails
    the primary key in this entity is OrderID + ItemID and a ProdID

    OrderID ties this item back to the order table
    ProdID ties the product ordered in OrderDetails refers to your product table

    Probably the best data modelling toolyou can use at this point is a sheet of paper,a soft pencil (HB) & rubber

    HTH

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by healdem
    I think you need to revist some of the documetnation n relational theory - its hard to get over in a forums, but the reference to Paul Litwins piece is well worth following up.

    often it said on data modelling concentrate on the key and only the key

    lets look at a a theoretical company

    it has an entity (table) called customer
    which comprises CustomerID (the key) and other rows such as customer name, address, tel etc...

    it has an entity called products
    whic comprises ProdID and other rows such as product descrpition, price case size etc....

    it has an entity called order
    which comprises OrderID and (unsurprisngly) other rows such as orderdate, deliverydate, CustomerID [so that you can identify who the customer is realting back to the Customer entity]

    it has an entity called orderDetails
    the primary key in this entity is OrderID + ItemID and a ProdID

    OrderID ties this item back to the order table
    ProdID ties the product ordered in OrderDetails refers to your product table

    Probably the best data modelling toolyou can use at this point is a sheet of paper,a soft pencil (HB) & rubber

    HTH
    I agree with Healdem. Sounds like you're having a bout with understanding the basics of relational database modeling. The site he pointed out to you is an excellent reference which will in fact answer your questions. If not that site there are others and even great books to help give you an understanding.

    BUD

Posting Permissions

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