0

I was wandering if it's a very bad idea to have one table that holds all the ids related to one entity?

For example let's say i have 3 tables each containing data about one user. Should i use an extra column called user_id in profile and settings table or should i use an extra table called user_index and make use of joins?

[1]USER
    id
    email
    password
    salt
    created

[2]PROFILE
    id
    full_name
    bio

[3]SETTINGS
    id
    max_packets
    delivery_day

[4]USER_INDEX 
    user_id
    profile_id
    settings_id

Will this make queries extra slow? Is it worth the extra code? Is it even a pattern? Maybe EAV?(I mean i saw something similar in Magento but i'm not sure if this is exactly the same). Can this provide any performance enhancement or just a 'OCD-feel-good' moment?

Currently using MySQL 5.7.26 and i was thinking about using InnoDB as a storage engine. The app is going to be written in PHP 7.3. OS Ubuntu 19.04.

Thank you!

  • 2
    Assuming a user can only have one profile and one setting the user ID, the common way to do this is to add the user ID in the profile and setting table. If they can have more than one setting a table with user ID and setting ID is the way to go and analog for the profile. The way you have in mind could be used if a user can have more than one profile and setting but these always come in pairs per user. – sticky bit Jul 21 at 15:28
  • @stickybit so basically this 'pattern' could be used for products in categories(one category - many products) or assigning multiple attributes from one table to one or more products and stuff like that? Is there a name for this 'pattern'? Is this going to cost much more resources(when querying)? – emma Jul 21 at 15:35
  • 1
    It's called an "n:m relationship" or "many to many relationship". I.e. one instance of an entity, e.g. a product, can be related to many instances of another entity (or the same entity) e.g. a category and the other way round -- e.g. a category having many different products. – sticky bit Jul 21 at 15:46
  • So if i want a user to have multiple settings records it'll be a one to many? Since its one user - many settings. But if i want to also let multiple users have the same setting it becomes a many to many? – emma Jul 21 at 15:50
  • Yes, that's it, exactly. – sticky bit Jul 21 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.