Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Mapping table design?

    Requirement:
    Given an external code value, I need to be able to look up the corresponding internal code.

    I need to create a schema to hold mappings between external and internal code values for each customer. There are different types of customers and often the mappings for the same type of customer will be the same. However, they can be customized on a per customer basis and therefore can vary. For example,
    Code:
    Customer 1 (Type Platform-A):
    ExternalCode    InternalCode
    100             orange
    101             orange
    102             cherry
    ...
    
    Customer 2 (Type Platform-A):
    ExternalCode    InternalCode
    100             orange
    101             orange
    102             cherry
    ...
    
    Customer 3 (Type Platform-A):
    ExternalCode    InternalCode
    100             apple
    101             orange
    102             cherry
    ...
    
    Customer 4 (Type Platform-B):
    ExternalCode    InternalCode
    SUPERORANGE     orange
    REGORANGE       orange
    CHER            cherry
    ...
    Here's an example schema that I think would work:
    MAPPINGS {MapId, CustomerId, ExternalCode, InternalCode}

    This would allow a select such as:
    SELECT InternalCode FROM mappings WHERE CustomerId = 2 AND ExternalCode = '100'

    I like the above design because the table is simple and querying it is simple. However, I'm wondering if having repeated data is bad design. In other words, if there are 100 customers of the same type, and suppose only 10 of them are customized, that means that 90 customers have identical mappings. And if a single customer has 50 mappings that's a lot of repeated data. So I'm wondering if there some way to indicate whether a customer uses a standard mapping or a custom mapping, and to treat it differently depending on that. Something like:

    CUSTOMER_TO_MAP {CustomerId, MapId}
    MAPPINGS {MapId, IsCustomMap, ExternalCode, InternalCode}

    This way multiple customers can share the same map. And if a custom map is needed, one can be added. However, it seems like this requires a two step query to get data: 1. Figure out which map to use 2. Use that map to get the InternalCode.

    Is this the right approach? Or is there a better way?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the simpler table

    and lose the MapID column -- make (CustomerId, ExternalCode) the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    As above but can't you also store the defaults in the same table under a separate CustomerId (say -1). Then do a left join on the defaults and the customer settings. Use ifnull to pull the customer setting if ones present and the default if not.

  4. #4
    Join Date
    Mar 2008
    Posts
    52
    What would that query look like?

  5. #5
    Join Date
    Mar 2008
    Posts
    52
    The largely repeated data has me concerned. Something seems bad about it.

    I'm leaning toward:

    CUSTOMERS_TO_MAPS {CustomerId, MapId}
    MAPPINGS {MapId, ExternalCode, InternalCode}

    To find out what code 'xyz' maps to for customer 5 use:

    SELECT InternalCode
    FROM Mappings
    WHERE
    ExternalCode = 'xyz'
    AND MapId = (SELECT MapId FROM CustomersToMaps WHERE CustomerId = 5)

Posting Permissions

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