DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Best practice parameter list

  1. #1
    Michael Culley Guest

    Best practice parameter list


    Hi all,

    We store a list of values in a table. These are general settings such a Company
    Name, GL Transfer Date etc.

    There is a disagreement at work about how this should be done. The two choices
    are:

    1) Create a table with a column called Control and a column called Value.
    Store the name of the item in the left column (eg "Company") and store the
    data in the right column (eg "ABC Pty Ltd")

    2) Create seperate fields for each and have one record in the table. eg a
    field called "Company", a field called "GLDate" etc.

    What is everyones opinion here?

    Thanks

    Michael Culley

  2. #2
    Anthony Jones Guest

    Re: Best practice parameter list

    Option 1 is definitely the right solution here. You don't want to have to
    go back and modify the database schema when you want to add a new general
    setting.

    --
    Anthony Jones
    Secta Group Ltd




  3. #3
    bob butler Guest

    Re: Best practice parameter list


    Anthony Jones <yadayadayada@msn.com> wrote in message
    news:3948d894@news.devx.com...
    > Option 1 is definitely the right solution here. You don't want to have to
    > go back and modify the database schema when you want to add a new general
    > setting.


    Definitely - it's much more flexible when you want to add, or remove, items
    later.




  4. #4
    Raymond R Cassick Guest

    Re: Best practice parameter list

    I might suggest taking it a bit further.. Something I have used in the past
    is:

    One table that holds the text descriptions for the list items, a unique (PK)
    index number, and a boolean value (lets call it 'Dead')

    tblListitems

    Key Name Type
    -------------------------------------------
    P Index Long
    ItemName Text
    Dead Boolean

    The next table will hold the actual data entries for this list when hey are
    recorded. You can keep one of these tables for an entire application usually
    by adding another column for the TYPE of list data stored at that row.

    tblListData

    Key Name Type
    -------------------------------------------
    P TableIndex Long
    F Index Long
    ItemValue (Depends on the type of data you are
    storing)


    Let em explain if it is not clear... In the tblListItems, I use the Dead
    column to kill old listitems from being selected. From time to time, these
    list items might get revised, old ones die, new ones are born, and we don't
    want an entire list of dead items that can be accidentally selected, but we
    do need them there for historical reasons. To fill your drop down list box
    just use a query that returns the list items that are NOT marked as 'Dead'.

    In the table that where you store the actual recorded values, simply use the
    Index of the ListItem as the foreign key, and store the value you want to go
    with it in the ItemValue column. I usually suggest using a text type here
    unless you really need to perform math on these numbers at the database
    level.

    If you do it right, you can use ONE of these tables across the entire
    application, and create a column to reference the TYPE of list data stored
    in that record, then create an index on that column to speed up queries.

    You might not like this way of doing it, some people tell me that it is way
    to much work. But, I feel that it keeps within the rules of normalization
    better than storing redundant text fields for each data item in the
    tblListdata table. The only time I would not use the tblListItems table as a
    foreign key was if the data I was storing in the tblListData table was of an
    ad hock, user defined on the fly type of data. But then that type of data is
    very hard to query on anyway so it would not matter tooo much.

    Hope I was clear here. If not, just ask for a clarification.



    "Anthony Jones" <yadayadayada@msn.com> wrote in message
    news:3948d894@news.devx.com...
    > Option 1 is definitely the right solution here. You don't want to have to
    > go back and modify the database schema when you want to add a new general
    > setting.
    >
    > --
    > Anthony Jones
    > Secta Group Ltd
    >
    >
    >




  5. #5
    Doug Reed Guest

    Re: Best practice parameter list


    I see pros and cons to each approach

    With # 1, the table definition will not change. As business needs grow,
    simply add rows to the table.
    The downside is it's a lot easier to delete a needed row from a table
    by mistake than to delete a column from a table definition by mistake.




    "Michael Culley" <m_culley@one.net.au> wrote:
    >
    >Hi all,
    >
    >We store a list of values in a table. These are general settings such a

    Company
    >Name, GL Transfer Date etc.
    >
    >There is a disagreement at work about how this should be done. The two choices
    >are:
    >
    >1) Create a table with a column called Control and a column called Value.
    >Store the name of the item in the left column (eg "Company") and store the
    >data in the right column (eg "ABC Pty Ltd")
    >
    >2) Create seperate fields for each and have one record in the table. eg

    a
    >field called "Company", a field called "GLDate" etc.
    >
    >What is everyones opinion here?
    >
    >Thanks
    >
    >Michael Culley



  6. #6
    Michael Culley Guest

    Re: Best practice parameter list


    Bob,

    But you end up storing things such as dates and numbers in text fields. Surely
    if someone said they used text fields to store a date you would think this
    not the best way to do it.

    Michael Culley

    "bob butler" <butlerbob@my-Deja.com> wrote:
    >
    >Anthony Jones <yadayadayada@msn.com> wrote in message
    >news:3948d894@news.devx.com...
    >> Option 1 is definitely the right solution here. You don't want to have

    to
    >> go back and modify the database schema when you want to add a new general
    >> setting.

    >
    >Definitely - it's much more flexible when you want to add, or remove, items
    >later.
    >
    >
    >



  7. #7
    bob butler Guest

    Re: Best practice parameter list


    Michael Culley <m_culley@one.net.au> wrote in message
    news:394a259f$1@news.devx.com...
    >
    > Bob,
    >
    > But you end up storing things such as dates and numbers in text fields.

    Surely
    > if someone said they used text fields to store a date you would think this
    > not the best way to do it.
    >


    That's a point against it, to be sure, but the fields we are talking about
    are typically read only once on app start up or first need and don't change
    during execution. The time lost to a conversion is, imo, trivial and
    doesn't offset the gain in flexibility. If it's a significant concern then
    you could always have a string table, a date table, a longs table, etc.

    As for storing as a string as a date, I've done that on more than one
    occasion. Using YYYYMMDD format internally I take no more space (assuming I
    do not need the time) and can sort or select on the field easily and don't
    have to deal with the DAO vs ADO vs whatever format for working with dates
    in SQL statements. I've generally stopped doing that but wouldn't rule it
    out depending on the circumstances.




  8. #8
    Michael Culley Guest

    Re: Best practice parameter list


    >are typically read only once on app start up or first need and


    Generally, this is true with items such as 'BackColor' or some other user
    setting, but dates are generally when something last happened - like last
    pay run or something, so change often. You then have problems with different
    PCs interpretting dates differently. I guess you could store all dates as
    1-Jan-2000 to elivate confusion, or as you said, stick to a format (DDMMYYYY)
    are decode it to a date yourself. This is messy but if you put it in a single
    routine in a 'UserSettings' object with a saveDate and GetDate method you
    put the messyness in one location and never have to think about it again.

    >>The time lost to a conversion is, imo, trivial and


    Either way would be about the same anyway. If you are writing to this table
    half a million times then you are doing something very wrong.

    >you could always have a string table, a date table, a longs table, etc.


    Yikes!

    Michael Culley

    "bob butler" <butlerbob@my-Deja.com> wrote:
    >
    >Michael Culley <m_culley@one.net.au> wrote in message
    >news:394a259f$1@news.devx.com...
    >>
    >> Bob,
    >>
    >> But you end up storing things such as dates and numbers in text fields.

    >Surely
    >> if someone said they used text fields to store a date you would think

    this
    >> not the best way to do it.
    >>

    >
    >That's a point against it, to be sure, but the fields we are talking about
    >are typically read only once on app start up or first need and don't change
    >during execution. The time lost to a conversion is, imo, trivial and
    >doesn't offset the gain in flexibility. If it's a significant concern then
    >you could always have a string table, a date table, a longs table, etc.
    >
    >As for storing as a string as a date, I've done that on more than one
    >occasion. Using YYYYMMDD format internally I take no more space (assuming

    I
    >do not need the time) and can sort or select on the field easily and don't
    >have to deal with the DAO vs ADO vs whatever format for working with dates
    >in SQL statements. I've generally stopped doing that but wouldn't rule

    it
    >out depending on the circumstances.
    >
    >
    >



  9. #9
    bob butler Guest

    Re: Best practice parameter list


    Michael Culley <m_culley@one.net.au> wrote in message
    news:394b5b6b$1@news.devx.com...
    >
    > >are typically read only once on app start up or first need and

    >
    > Generally, this is true with items such as 'BackColor' or some other user
    > setting, but dates are generally when something last happened - like last
    > pay run or something, so change often.


    Yes, but you still would probably only read it when you did a pay run or ran
    a report or a few other occasions. I definitely agree that if it were
    something that I needed to read/update many times I'd look for the most
    efficient way I could do it and this may not be appropriate. I had based my
    earlier response on the description of these being general information
    parameters like fiscal year ending date which typically are static.

    > You then have problems with different
    > PCs interpretting dates differently. I guess you could store all dates as
    > 1-Jan-2000 to elivate confusion, or as you said, stick to a format

    (DDMMYYYY)
    > are decode it to a date yourself. This is messy but if you put it in a

    single
    > routine in a 'UserSettings' object with a saveDate and GetDate method you
    > put the messyness in one location and never have to think about it again.


    That's exactly what I've done on a few occasions where I thought it was
    appropriate (except YYYYMMDD so that sorting would be easier). If you don't
    use the database's native date format you defintely need to define your own
    unambiguous format and be consistent with it.

    > >>The time lost to a conversion is, imo, trivial and

    >
    > Either way would be about the same anyway. If you are writing to this

    table
    > half a million times then you are doing something very wrong.


    No argument there. I wouldn't do it if I had to convert the data
    repeatedly.

    > >you could always have a string table, a date table, a longs table, etc.

    >
    > Yikes!


    Verily, but it is an option and I just thought I'd throw it on the table.
    I'm not really an avocate of it.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center