Skip to Content

Data Modelling Question

Hi, No points to be found here, just looking to strike up conversations.

I need to create a new characteristic say "Survey Status". When creating this would the best way be to create a 2 length NUMC or say a 6 length character field that would be a more meaningful code in a pull down?

I think it is better to use the NUMC that whay the SID will be the same number and less joins are required somewhere down the line. Can anyone comment more on the pros or cons of either way?

Examples

01 - Survey in Preparation

02 - Survey is Open

03 - Survey is Active

03 - Survey is Closed

or

PREP - Survey in Preparation

OPEN - Survey is Open

ACTIVE - Survey is Active

CLOSED - Survey is Closed

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 28, 2005 at 03:58 AM

    Kenneth,

    I prefer the coded (01, 02, ...) with the corresponding text loaded separately. If the business changes the meanings (which never happens) you just have to reload the text. A dropdown can show either key or text, so there will be no issue there.

    I would not be concerned with the SID values.

    Cheers,

    Dave Fox

    MassMutual

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 28, 2005 at 04:42 PM

    We could debate the character vs numeric data type issue as to what would work best for the underlying database if we were worried about it's use in an index, constraints on the values entered, etc.

    But I don't think I would consider the SID issue a factor. A SID table still exists and still must be joined to in the query.

    You might be able to make the case that having the characteristic value suggestive enough that you wouldn't need text data might save a tiny query to get the text values, but that is usually a very small query that is able to run on the appl server and take advantage of SAP buffering - it's not part of the the main query that reads the fact table or ODS and runs on the DB server.

    If it's numeric, you might save a couple of milli-seconds in not having to perform a number range lookup to get the next available SID value, but if there is a very tiny set of values for this code, you wouldn't be performing this action very often either.

    The real question, I think is the loss of flexibility of trying to rely on the characteristic key value and not having text master data. What happens when there become a couple of variations of the OPEN status that can not be easily expressed with 6 characters? What does a user make of OPEN2 and OPEN3? Wouldn't the ability to have texts for these values be helpful, e.g.

    Open2 = Open account without any activity

    Open3 = Open account with activity

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.