Results 1 to 7 of 7

Thread: Hello!

  1. #1
    Join Date
    Jan 2014
    Posts
    4

    Hello!

    Well first of I would like to say hello to everyone, I am very hopeful that I will become a regular in this community as I am very interested in database design and maintenance. Secondly I am hoping to get some insight into what would be the most efficient way to handle the data I am trying to collect/record. Basically I am attempting to for arguments sake log the short time period rentals of items. The items will always be returned within 20-60 or so minutes from the time they were taken out. I also want to count how many times a day they were taken out, and if one or two issues were experienced while the item was out. I was thinking the best way for this to be handled would be columns as follows:

    Code:
    ID    DisplayName    1/15/2014    1/16/2014
    and that every day the application(this will be updated through a mobile application, the application will have to check if the current day exists as a column and create it if it does not already exist. Within the date will be all the data for that day comma delineated. So if the item went out 3 times and there were no errors with the product the first two times but there was an error the third time it would look as follows:

    Code:
    3,8:00,8:15,0,11:21,11:36,0,15:02,15:04,1
    So the 3 indicates how many times item went out, then the application would parse time out, time in, any errors, rinse and repeat.

    Does this seem like an efficient way to track the data? I have never really designed a database where the columns are being created in an ongoing fashion.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    It is perfectly fine to display data the way that you're describing but it is NOT a good idea to store the data that way. It is easy to aggregate/format data (think of counting the change in your pocket), but very difficult or impossible to separate/unformat that data (think of guessing which coins make the 93 cents).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2014
    Posts
    4
    Hi Pat,

    Thanks for the speedy and helpful reply. I think it will be less than ideal for parsing out the information but it will all be automated by code that will look for the first number, then select the data between the commas as it instructed. I am more than happy to hear any suggestions as to a more efficient way to manage this data if you can think of a better way.

    -KC

  4. #4
    Join Date
    Jan 2014
    Posts
    4
    Would it be a better idea to have each piece of inventory be its own table and then have the table look like this for each inventory?

    Code:
            1OUT  1IN  1ERROR 2OUT 2IN  2ERROR 3OUT 3IN 3ERROR 4OUT 4IN 4ERROR
    1/15/14
    1/16/14
    etc

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I would logically structure the table as item, date, in, out, error. The process of converting that format to either of the ones that you've proposed above is pretty straightforward. You might consider adding the time to your date attribute even if you don't display it, you may want to have more than one measurement per day!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2014
    Posts
    4
    I'm sorry I'm being so dense this actually is starting to make sense and I'm not sure why it took so long for me to understand.

    Code:
    ITEM    DATE    IN      OUT     ERROR
    0001    1/15/14 8:00    8:36    0
    0001    1/15/14 10:02   10:12   0
    0002    1/15/14 11:13   12:15   0
    0001    1/15/14 15:41   15:56   1
    I just guess I haven't done a lot of queries or anything of database code but I presume it would be easy to parse out all the data for one item, day by day if necessary. When you say more than one measurement per day were you just referring to the IN OUT ERROR maybe having a second IN OUT ERROR?

    Thanks so much for making something that seemed so complicated seem a little bit simpler.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    If you look at your DATE column, you already have four sets of values on the same date so you've provided part of what I was trying to explain.

    I hadn't considered using IN and OUT as times, I thought that those were counts.

    You are doing fine!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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