MySQL: Complex data structuring and querying

Question

I'm having a bit of trouble figuring out what would be the best approach for creating the database tables for the following (rather complex) data structure and I'm hoping that someone with more experience than me could help out. The main reason for my trouble is both normalization and trying at all costs to avoid querying inside loops.

location 1 (location_group_name, owner_id, admin_id)
  location 1.1 (name, address)
     location 1.1.1 (name)
        // DEVICE LIST
        device 1 (manufacturer_id, model_id, serial, purchase_date, service_date)
            Battery (manufacturer_id, model_id, purchase_date, service_date)
            Accesory 1 (manufacturer_id, model_id, purchase_date, service_date)
            Accesory 2 (...)
            Accesory n (...)
        device 2
            Battery
            Accesory 1
            Accesory 2
            Accesory n
        device n

        // STAFF LIST
        person 1 (name, email)
           qualification 1 (type, date)
           qualification 2 (...)
           qualification n (...)
        person 2
        person n
     location 1.1.2
     location 1.1.n
  location 1.2
  location 1.n
location 2
location n

I am currently thinking of inserting each device and person as a serialized multi-dimensional array, but I am not sure how well that will work due to the fact that a cron job script will check the service_date field every day and send automated emails to the admins and owners of the location groups if certain criterias are met. To complicate things further, the devices data should be searchable by model as well, in case of a recall, or by serial_number for quick find in case the user has lots of devices added and/or doesn't exactly know its location.

If it's not too much to ask, I would also like to see a query example for the proposed data structure (only because I figure it would probably use joins and I am quite unfamiliar with them).

Also if there is any extra info you need, please feel free to ask and I will gladly elaborate.

Thank you in advance, any input will be more than appreciated !

Answer 1

Looks like you have a one to many relationship between location1.1.1 and device, and between location1.1.1 and person. I think you'll end up querying in a loop, or you're going to do a few large queries and loop to merge them together.

Don't freak out about querying in loops. With prepared statements they don't have to be that slow.

Answer 2

I'm not entirely sure what you're trying to do here, but why all the hierarchy? This is some sort of store/device/employee model, correct? In a relational database, you can separate discrete information into their own sections and relate them on other tables. So if you have stores/devices/employees, you can have a "stores" table, a "devices" table, an "owners" table, an "employees" table, a "stores_employees" table, an "owners_stores" table, a "devices_stores" table (and you could track inventory in that), an "employees_qualifications" table, and so on..