Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Red face How to map this to relational db?

    Hi. I am a novice in terms of db design and would like to ask for advices here...

    Suppose I have two datatables, MEMBER and HELPER, each of them has PK User_ID (pretty standard).

    As a MEMBER, there are information like Username, Password, Email, etc. I want to store in it (1-to-1). In HELPER, there are also similar information I would like to store. The number of fields in both MEMBER and HELPER differs slightly though.

    I plan on creating a third table, MEMBER_DEFINITION, with 2 columns (Col1 - PK: User_ID; Col2 - MEMBER_TYPE varchar: 'member' or 'helper'). This will map the relation that 'a member by definition is either a member or a helper'.

    My question is, should I keep the fields that are common in MEMBER and HELPER the way they are (same column names) or should I put them inside my third table MEMBER_DEFINITION? I would like to see the pros and cons of doing each way and since I am no expert I would like to ask for your generous help!

    Thanks in advance...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: How to map this to relational db?

    Originally posted by sTe810
    Hi. I am a novice in terms of db design and would like to ask for advices here...

    Suppose I have two datatables, MEMBER and HELPER, each of them has PK User_ID (pretty standard).

    As a MEMBER, there are information like Username, Password, Email, etc. I want to store in it (1-to-1). In HELPER, there are also similar information I would like to store. The number of fields in both MEMBER and HELPER differs slightly though.

    I plan on creating a third table, MEMBER_DEFINITION, with 2 columns (Col1 - PK: User_ID; Col2 - MEMBER_TYPE varchar: 'member' or 'helper'). This will map the relation that 'a member by definition is either a member or a helper'.

    My question is, should I keep the fields that are common in MEMBER and HELPER the way they are (same column names) or should I put them inside my third table MEMBER_DEFINITION? I would like to see the pros and cons of doing each way and since I am no expert I would like to ask for your generous help!

    Thanks in advance...
    This is pretty similar to the question on "Horizontal Partitioning", so rather than answer this specificaly I'll direct you to the answer I gave there:

    http://65.61.175.198/t886878.html

  3. #3
    Join Date
    Feb 2003
    Posts
    15

    Lightbulb Re: How to map this to relational db?

    Thanks for your reply. So the answer is just like those of all computer questions... it depends. =>

    An additional question. Suppose I want to stick to 3NF and map the common fields in my MEMBER_DEFINITION table. Now, in my original MEMBER and HELPER, they each have a field 'Phone'. Suppose I want to make MEMBER's to have this field optional and HELPER's to be mandatory. Before they are in different table so that's no problem. But now should this field belong to the new table MEMBER_DEFINITION?

    Greatly appreciate your help~

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: How to map this to relational db?

    Originally posted by sTe810
    Thanks for your reply. So the answer is just like those of all computer questions... it depends. =>

    An additional question. Suppose I want to stick to 3NF and map the common fields in my MEMBER_DEFINITION table. Now, in my original MEMBER and HELPER, they each have a field 'Phone'. Suppose I want to make MEMBER's to have this field optional and HELPER's to be mandatory. Before they are in different table so that's no problem. But now should this field belong to the new table MEMBER_DEFINITION?

    Greatly appreciate your help~
    A check constraint can be used to enforce a rule like that:

    check( type='MEMBER' or phone is not null )

Posting Permissions

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