1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL Table Design

Discussion in 'MySQL' started by Trav H, Dec 12, 2013.

  1. #1
    Hi All,

    I am in the process of creating a data logging database, which will record analytical results from a number of sensors (e.g. Temperature, Humidity, Dust, WindSpeed, WindDirection) at least to start with.

    My issue is that the sensors are not always placed at the one location, but rather the sensor would be 'assigned' to a client for a period of time, then returned to the assignable pool, to be reassigned to a client at a later period of time.

    So my question is, how is the best way to structure the database in order to allow me to assign a specific instrument to a specific client / user / site.

    My thought on the db structure is as follows

    Instruments
    -InstrumentAutoID
    -InstrumentName
    -InstrumentSerial
    -InstrumentCategory
    -InstrumentUnits
    -InstrumentReportMin
    -InstrumentReportMax
    -InstrumentReportAve
    -InstrumentReportStDev
    -InstrumentComments

    Clients
    -ClientID
    -ClientName
    -ClientContact
    -ClientAddress
    -ClientSuburb
    -ClientEmail

    Sites
    -SiteID
    -ClientID
    -SiteName
    -SiteAddress
    -WarningEmailAddresses

    RawData
    -EntryID
    -InstrumentAutoID
    -DateInserted
    -InstrumentType
    -InstrumentValue

    EquipmentAssignment
    -AutoID
    -InstrumentAutoID
    -ClientID
    -StartDate&Time
    -EndDate&Time

    So looking at the above (as an example), is that the best way to do it to assign the equipment to a site (and therefore client) between a date range, and then just pull the results for a given client based on the date range in the equipment table and if that is the case, how can i best pull the client's data when there would be no real join.

    Is it better to do a nested query where the below could be used to get the StartDate&Time and EndDate&Time and then look at the results that are inbetween that date. Also further to that what is the best way to control my data so that one instrument cannot be assigned to multiple clients / sites within the same date range?

    I would be working with PHP, so my thought was that I would have to look at the table and do it programmatically since the dateTime would not be a unique field.

    Thanks for your tips / thoughts / suggestions .

    SELECT StartDate&Time, ClientID, EndDate&Time from EquipmentAssignment WHERE ClientID = x
     
    Trav H, Dec 12, 2013 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    You could work with what you have, and just do a join on the other tables to get the data you need from each of them.
    Something like SELECT t1.*, t2.* FROM EquipmentAssignment t1 LEFT JOIN Clients t2 ON t1.ClientID = t2.ClientID WHERE ClientID = XX

    As for avoiding multiple assignments in the same time-period and such, I suggest you control that in the front-end, based on whether or not the given piece of equipment is currently available or not (have one column with Assigned (bool) 1/0) and just check for it being 1 or 0.
     
    PoPSiCLe, Dec 13, 2013 IP
    khodem likes this.
  3. Trav H

    Trav H Greenhorn

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    Thank you for the suggestion, it is most appreciated.
    I am wondering (and forgive me if it is a newbie question) but with assigning a date range to an item, is there a way to do a SQL select for between when there may be no end date.

    So for example if I have client A who temporarily has equipment that i assign from a date of say 10/12/2013 to 20/12/2013 then I could do a search where it is the current date is BETWEEN startdate and enddate which works fine, but if I have client B who has the equipment permanently from the 10/12/2013 indefinitely then will a between actually work when there is no end date, or is there a better way to structure the SQL to return the equipment.

    For example:

    SELECT t1.* FROM EquipmentAssignment WHERE DATE(now())BETWEEN DATE(EquipmentAssignment.startdate)AND DATE(EquipmentAssignment.enddate);

    The above should work for Client A, but will it work for client B?, and if not how could I restructure this to work for both examples (client A with end date, and Client B with no defined end date)

    Thanks for the previous help, and your thoughts.
     
    Trav H, Dec 16, 2013 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    The best way for such items would probably be to assign a future date - ie set it to max date-value (sometime in the year 2038 or something, if I remember 32bits UNIX time properly?) (Not sure if this applies to regular date-variable cells in the table). Anywho - if you do that, all you have to do, if the system is still running this far into the future, is to update all rows with that set date (one thing you can do, for instance, is set this date to a non-existing date, for instance 29. feb. of a non-leap year, so to avoid any trouble with assigning said date later on).
    This might not be the best practice, but it works, and doesn't involve any code-changes.
     
    PoPSiCLe, Dec 17, 2013 IP