Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best way to organise MySQL tables?

    I am trying to think of a cleaner way to store information in MySQL tables.

    Here is an example:

    Website where people look at pictures of dogs.

    Have 1 table that stores the location of all the pictures of dogs and information about that dog (breed, colour, age).

    2nd table stores search criteria that the user has selected. I.e. they only want to see pictures of black Labradors.

    PHP code then collects the id of all the pictures in table 1 that match the user's criteria in table 2.

    The user is then shown all these pictures 1 at a time. They can then click a YES or NO button that will determine whether they see this picture ever again.

    Another table then stores the id of the picture and whether they clicked yes or no. Next time the user comes back to the site, they are only shown the pictures of dogs that match their criteria from table 2 and one's they have not yet said NO to.

    However, this would require a YES/NO table for every single user. This is where the number of tables gets a bit too large. I am trying to find a simpler way to organise this data.

    Suggestions?

    Cheers,
    Sam

  • #2
    New Coder
    Join Date
    May 2011
    Posts
    43
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Biggest part of designing a DB is learning about normalization and normal forms.

    I think you should have an attribute like "active" and set animals to be shown active and the others not.

    What attribute are you using as the primary key/foreign key? Or are you just comparing id's?

    The second table should have an attribute/property that is linked to an attribute/property of the first table.


  •  

    Posting Permissions

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