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 6 of 6
  1. #1
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts

    best way of storing multiple items against a record and being searchable

    OK long title.

    I'll try my best to explain how i have things setup at the moment and hopefully you guys can make some suggestions on the best way to do it since my head is not really working it seems today.

    I have many companies each stored as a row in a table a
    cid,lots of other columns

    I have many currencies each stored as a row in table b
    id, symbol, currencyname

    Now what I want to do is assign 1-many currencies against each company.

    Option1
    So my first thought was have an additional column in table a and store a comma list of selected currencies in there. However this poses a couple of problems
    1) I need to search on currencies traded by the company due to this column needing to be a varchar to store a csv I'd need to do a like % % search this would lead to mis matching as 1 would match 10 and 11 and 21 etc.
    2) I also need to display a series of checkboxes on the company details page one for each currency available and have them checked if the company currently trades in it.

    Option 2
    I thought use a serialised array but then I read up on data serialisation and I can't search on it the way I need to so that was out.

    Option 3
    Have a new table table c which contains simply the company id and the currency id. so each entry in table a will have a one - many relationship with table c
    not many problems with this idea as far as I can tell it'll work but makes data display very very slow as for displaying the checkboxes on the company details page i'd need to do a master select from table b and for each row do a select from table c where currency id= and companyid= to work out if i should be checking the boxes and again on updating the boxes its some funky checkbox magic again.


    Are there any other options out there i'm missing or something far more simple?
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #2
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    Option 3 is the right way to go about it. Using one of the first two will make things awkward
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • Users who have thanked JAY6390 for this post:

    hinch (02-11-2010)

  • #3
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    figured as much i hate "messy" solutions was hoping there was an easier way i'd overlooked
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #4
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    Not sure why you think it's messy to be honest, but it's the standard way of doing HABTM relationships
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Quote Originally Posted by JAY6390 View Post
    Not sure why you think it's messy to be honest, but it's the standard way of doing HABTM relationships
    Agreed, any many-to-many style table should include a flattening table between them. In this case, the company id and the currency id. This is part of you're normalization process and will (according to the info provided) style a 3NF database. There are cases when normalization is not feasible, but these are very rare (my brother for example works for a corp that requires 2NF on half of their tables due to the prohibitively expensive write speeds of the 3NF style).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #6
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    reading and writing speeds are one of my concerns which was why I was thinking of a way of doing it without a 3rd inbetween table I just couldn't think of a way around it though without having to do some insane array functions and some mad skillz javascript front end for array generation
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com


  •  

    Posting Permissions

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