#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    4
    Rep Power
    0

    Question DB Design Issue?!


    Hi to ya all!!!

    This is my first post to this forum and Im a total newbie to PostgreSQL and DBs in general ...

    Im trying to create a DB for my work wich will handle some stuff that we sell ... the problem Im having is that we have both internal and exeternal customers and I dont know how to reflect this in the DB design. For the internal customers we got a couple of master departments and some department under that.

    For the external customers I just want to reference to the companies name.

    But in the end I would like to have one big Customer table that I can refer to when adding a buy.

    The columns that are relevant for both the custom categories are the following:

    user_ID
    username
    password
    first_name
    last_name
    e-mail
    phone
    mobile

    The columns that defer for the two customer categories are the following

    master_dep |____ These are for the internal customers
    child_dep | A user can be memeber of say social/family

    Note that both columns are are alway relevant for the interna customer.

    For the external customer only one column identifies it memebership.

    company

    Well, hope I have made this clear how it works and what I need to get done ... any help is welcome

    /Roger
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    4
    Rep Power
    0

    Cool Think I solved it today =)


    Im putting all my customers into one table ... if both organization and suborganization got values, its an internal user, if only organization got a value, and sub org. is NULL its a external user.

    Hope this will work =)

    /Roger
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Yes, you're going in the right direction. It's always best to generalize and abstract your information. If you separated your customers into two tables, you would be setting yourself up for unforseen problems later.

    It sounds like you need to spend a little more time thinking about your table layout, though. It might be better for you to have yet a third table which identifies parent-child relationships between customer records. The basic rule of thumb for normalization is to not have a column in your table which will be unnecessary for a significant number of records.

    Also, If your existing data isn't enough to properly separate your internal and external customers, you could just include a simple column called 'cust_type' or something like that. This would allow you to have more customer types in the future. You should include an external validation table called 'cust_types', which lists the possible customer types, and which you can use as the drop-down box for data entry. This keeps you from having incorrect information placed in that column.

    If you don't catch my drift, let me know, and I will explain these ideas in more detail.

    I will also recommend a couple of good books, like I did in a previous post:

    "Database Design for Mere Mortals" (this gives you a great basic approach to databases in general) and "PostgreSQL Developers Handbook".
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    4
    Rep Power
    0

    Thanx rycamor!!!


    Thanx for the ideas ... I will consider this. As Im new to this their is a lot to know =)

    /Roger

IMN logo majestic logo threadwatch logo seochat tools logo