#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    15
    Rep Power
    0

    Advice in MySQL Database Structure


    Hello guys,

    I have a project in web development for an online inventory management system (PHP, MySQL, JavaScript, jQuery).

    The concept is as follows:
    The web-app will be available for everyone online, to create an account and manage the inventory. Some features of the web-app will be items, users, orders, customers and suppliers.

    So, the questions is: Do i have to create a database for each account or store all the data in the same database/tables and categorize them by a unique key?

    Also, i have a shared hosting plan and the "CREATE DATABASE" query is disabled. So, I have to create manually each database.

    Thank you in advance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,396
    Rep Power
    1688
    Same database. Same tables. Don't even think about other options!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    15
    Rep Power
    0
    Originally Posted by SimonJM
    Same database. Same tables. Don't even think about other options!
    Thanks for the response. What about MySQL performance and how it will be able for each user to import/export any data?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,396
    Rep Power
    1688
    Unless you go to multi-millions of records I doubt you'll have much of a performance issue, assuming correct indexing.

    You'll have one table for all the 'static' things: users, suppliers and items, etc. Other tables will include one, or more, of the unique keys of those, along with other columns. An order, for example, would have it's own unique id, along with the id of the user, plus date of order, etc. You'd also have another table which acts as a bridge between order and items (including their respective ids) as well as any additional column you may need, such as number ordered, and price over-ride, etc.

    You just write your queries accordingly, JOINing the tables together and using relevant WHERE conditions to limit the data returned.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    15
    Rep Power
    0
    Originally Posted by SimonJM
    Unless you go to multi-millions of records I doubt you'll have much of a performance issue, assuming correct indexing.

    You'll have one table for all the 'static' things: users, suppliers and items, etc. Other tables will include one, or more, of the unique keys of those, along with other columns. An order, for example, would have it's own unique id, along with the id of the user, plus date of order, etc. You'd also have another table which acts as a bridge between order and items (including their respective ids) as well as any additional column you may need, such as number ordered, and price over-ride, etc.

    You just write your queries accordingly, JOINing the tables together and using relevant WHERE conditions to limit the data returned.
    That's what i did. I just needed some advice about the accounts! OK , thank you Simon

IMN logo majestic logo threadwatch logo seochat tools logo