Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    designing overlapping generalization hierachy

    I am in the process of re-designing the member database in our company. Currently there is a problem with calculating the "point" each member has. We used to have only one kind of point for each member, but now it seems we will be expanding and each member may have one or more kinds of "points":

    for example:

    a consumer can be either a member or a visitor
    a member could have one or more kinds of points

    the schema used to be:

    consumers(consumer_id(pk), consumer_email)
    members(consumer_id(fk), member_id(pk), point)

    now the point attributes will be expanded. So I can either add a new column for each new kind of point, or move the point attribute to a new table called points

    points(member_id(fk), point_type(composite pk with member_id), point)


    which solution would be better performance-wise or design-wise?

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

    Re: designing overlapping generalization hierachy

    Originally posted by mchih
    I am in the process of re-designing the member database in our company. Currently there is a problem with calculating the "point" each member has. We used to have only one kind of point for each member, but now it seems we will be expanding and each member may have one or more kinds of "points":

    for example:

    a consumer can be either a member or a visitor
    a member could have one or more kinds of points

    the schema used to be:

    consumers(consumer_id(pk), consumer_email)
    members(consumer_id(fk), member_id(pk), point)

    now the point attributes will be expanded. So I can either add a new column for each new kind of point, or move the point attribute to a new table called points

    points(member_id(fk), point_type(composite pk with member_id), point)


    which solution would be better performance-wise or design-wise?
    Almost certainly the new table, because:

    1) It allows for the introduction of new points types in the future.

    2) It simplifies processing of points - for example instead of

    IF p_point_type = 'A' THEN
    UPDATE members SET point_a = point_a+1 WHERE member_id=1;
    ELSIF p_point_type = 'B' THEN
    UPDATE members SET point_b = point_b+1 WHERE member_id=1;
    ELSIF p_point_type = 'C' THEN
    UPDATE members SET point_c = point_c+1 WHERE member_id=1;
    ... etc.
    END IF;

    you will have:
    UPDATE points
    SET point = point+1
    WHERE member_id = 1
    AND point_type = p_point_type;

Posting Permissions

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