Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009

    Unanswered: Table Design: Assistance Requested

    Hello Greetings to everyone.

    My Name is Sakthivel, I am from Chennai, India. I need some assistance in designing a table.

    I use a table for collecting the information as
    ClientID - Autonumber
    ClientName - Text
    Address1 - Text
    Address2 - Text
    City - Text
    Pincode - Text
    Phone - Text

    Now the issue is suppose the Customer enrolls his brother / sister, I want the customerid to contain something like the cusomerid + some suffix so that i can pu the new client also under the same family.

    Kindly someone help me in this regard.

  2. #2
    Join Date
    Apr 2005
    Zagreb - Croatia
    I can suggest an AUTONUMBER (as prefix instead of suffix), it'll be;

    #CID Autonumber
    #CustomerID Number (or txt).

    and s.o.

  3. #3
    Join Date
    May 2009
    Provided Answers: 1
    Sakthivel, I am not sure if I am reading this correctly. You indicate you want to combine CustomerID and 'Family Group code' to indicate people in the same family.

    Is this in one column? For ex:


    where A and B are two different Family groups.

    If it is, this is generally a bad design as two different pieces of information are put into one column. If you consider Husband and Wife as the same family, what would you do if they got a divorce? Since they would no longer be the same Family, you would need to change their CustomerID instead of just updating a Family Group code.

    PS Not sure what this would do for an entire family of Husband, Wife, Son, Daughter. After a divorce, the ex-Husband and ex-Wife would not be the same Family Group but the ex-Husband and children would be the same Family Group and the ex-Wife and children would be the same Family Group but the ex-Husband and ex-Wife would not be.

    Before a divorce, if all were Family Group A, then

    Husband = Wife = Son = Daughter
    A = A = A = A

    After divorce:

    Husband not equal to Wife
    A not equal to B

    ex-Husband = Son = Daughter
    A = A = A

    ex-Wife = Son = Daughter
    B = B = B

    The Son and Daughter can't be both A and B but the ex-Husband and ex-Wife can't be equal.

    (And I not even going think about either one (or both) getting remairred).

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I'd suggest you push the address down to a sub table
    have a fk pointing to the same table identifying a familial realtionship

    relatedto 'fk to nullable field
    'if null then this person is the "main" or initial contact

    ID 'autonumber column
    addresstypeID 'fk to an addresstype table which identifies the type of this address
    personID 'fk to persons
    ...address columns

    PK of tblAddresses is ID & addrestypeID & PersonID (ie
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2009

    Requesting Assistance

    I am very sorry for the confusions involved. Let me make it a bit clear as far as I know. I want to design a table to hold the customers contact information, which includes the details, name, address1, address2, pincode, city. Assume that the 1st record points to the father. Later when the son wish to enroll his details also need to be collected for which the contact information would be different. Now If the father's customer id is something like 712645-001, I want to use the son's customer id to be 712645-002.

    This 712645 (first part) should be auto generated, and the suffix must be fetched +1 greater than the last record. ie if a record 712645-002 exists, the new id should be 712645-003.

    Please advice me the best way to do this.


  6. #6
    Join Date
    Jun 2009

    Table Design Help

    I need help designing a table. Kindly help me on it.

    The scenario is to collect customer informaiton like address, city, state, zipcode to a table. Customer ID is something line XX-YYYYYY-ZZ where XX is a category to be selected from a combo box, YYYYYY is autogenerated number and ZZ is if the customer id already exists 01 else it will be incremented by 1, ie the new value should become 02.

    The reason I need this type of table design is there might be a family where the parent will be my main customer, and when their son or daughter also enrolls, I'll add them to the same family group.

    If the parent is given the Customer ID as
    A1-000001-01 then the son will be enrolled as A1-000001-02 and the daughter will be enrolled as A1-000001-03.

    If a new customer comes and enrolls whom doesn't belong to the above family, then his Customer ID should be generated as A1-000002-01 and his family members will have the ID as A1-000002-02.

    I am very much confused when I was asked to do this hence I am requesting assistance.


Posting Permissions

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