Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2014
    Location
    Illinois
    Posts
    6

    Thumbs up Unanswered: Primary Keys through multiple databases (MySQL DBMS)?

    For sake of argument, lets pretend that I am creating a mobile application that customers use. Databases are only allowed to be up to 1GB in size maximum (something to consider as the databases grow with new users/customers and new items. With this application, a customer will log in with their own personal credentials and is then able to add/remove items in their "personal inventory" from a list. Each item on the list has its own set of attributes/data within itself.

    In case you are lost, lets put this into a practical example:
    - You are a customer. You log into the application with your personal credentials (username/password)
    - You go into your personal inventory in the application and notice you have a: full deck of playing cards
    - The attributes of the item (full deck of playing cards) are: card box, 52 cards

    I want to create one database that houses all data pertaining to the actual customer and then a completely different database that houses all of the items this application will have in it along with their attributes.

    Am I able to have the database reference you "user" from the "user database" and connect you to your inventory and also reference items that you have in your inventory from a different database? Meaning your "user_id" is cross referenced across multiple databases? If so, what pros and cons are there.

    Thank you in advance for anyone who not only understands my question(s), but also answers it/them!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you need multiple databases for a customer and their inventory?
    I could see why perhaps you might require different types of inventory tables (say through the sub/supertype model). I could see that if the inventory is more of a list ie you don't actually need much structure in the list then a hybrid EAV may work (keep the inventory structure for common data, and use an EAV extension for the specific (But largely irrelevant items). but if you need to search on those extended list data then you need to impose some form of structure, somehting which is very tricky to satisfactorily implement using EAV
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2014
    Location
    Illinois
    Posts
    6
    Thank you for your reply Healdem!

    The "user database" would need to be referenced across multiple databases/tables, some through the use of the application, and some through the use of the website. Also, 1 database is only allowed to be up to 1GB.

    The reasons I would consider multiple databases are:
    1 - Because of the cap/limit on each database (1GB)
    2 - The customer's inventory will always be changing. The customer will be able to add/remove items from their own inventory.
    3 - The "items database" will always be changing. The admins will be adding and removing items frequently.
    4 - Items in the "item database" will have different types of the same item. Example: 1 full deck of playing cards - blue and 1 full deck of playing cards - red
    5 - The "items database" will be used by all customers. multiple customers will have "item a", and multiple customers will have "item b". Many customers will also have "item a", "item b", and "item c" in their inventory.
    6 - Having multiple databases will keep elements separate and will keep a certain structure. That way, if there is an issue, we will know where to look in order to correct the issue.

    I completely agree with you regarding the sub/super type tables. That will make a lot of the information that will be used to create the "items database" much easier to manage. And, in order to achieve 1NF and 2NF, sub/super type is the way to go. Does having multiple tables impact performance? Would having multiple databases impact performance? Especially if there are hundreds of customers querying the database at the same time?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    1Gb is a feck of a lot of space.
    If you are running out of space, then that suggests there should be enough money (ie enough users who think this application is worthwhile and therefore prepared to pay or enough users to be attractive to advertisers to pay on the users behalf)

    unless you have some clear definitive metrics of why you are going to require a separate inventory database for each user then it seems an unecessary complication
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2014
    Location
    Illinois
    Posts
    6
    Quote Originally Posted by healdem View Post
    unless you have some clear definitive metrics of why you are going to require a separate inventory database for each user then it seems an unecessary complication
    I agree that 1GB is a lot of space.

    We would only use 1 database that is dedicated to all of the items and 1 database dedicated to all of the users. Each user would have their own row (and associated "UserId" key) in an inventory table under the "user database" that pulls the data (AKA - the items in their inventory) from the "item database".

    Are we able to reference a user across multiple databases utilizing primary keys?

Posting Permissions

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