Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16
  1. #1
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    I have a table called concerts with two fields, wantuserids (the userids of those who wanted to see a concert) and wentuserids (those who went to a concert). Right now, because the fields must be able to store more than one userid, I've been serializing a PHP array and saving it. However, that makes it extremely inefficient to later search the table for, for example, all the concerts that a certain user wanted to see.

    Any ideas on how to change the schema? This one has me stumped.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  2.  

  3. #2
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    Why not make a new row for every userid per concert?
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  4. #3
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    But if there are hundreds of concerts and thousands of users make selections, then the table could be quite huge...
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  5. #4
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    Seems to me, better to have thousands of rows instead of thousands of values in one row...

    Or if you'd like, do it oppositly, make the key the userid, and have a colomn for visited and wanted, and serialize the concert names...
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  6. #5
    d31
    d31 is offline
    Member
    Join Date
    Oct 2003
    Location
    Sydney, Australia
    Posts
    39
    Member #
    3423
    I think i have come up with a solution for you.


    You need to break this scenario up into 3 tables.

    Table 1: tblConcert
    Table 2: juncConAtt
    Table 3: tblConAttend
    NB:I like to name my tables with a tbl prefix, and junction tables (tables that act as the bridge for a many to many relationship) with a junc prefix followed by shortened table names)

    tblConcert:
    - concert_id
    - any other relevent fields

    juncConAtt:
    - concert_id (PK FK non unique field)
    - att_id (PK FK non unique field)

    tblConAttend:
    - att_id (PK)
    - user_id (PK FK)
    - concert_id (PK FK)
    - attend (bit value, 1 for yes, 0 for no)

    Basically that's the setup.

    Your tables will grow as more people sign up and what not...but it's better to have thousands of rows than to have thousands of values for one row. (As stated above)

    This is off the top of my head so you might want to play around with it a bit more.

    Edit:
    eh where's my "Award dollars button?" :/

  7. #6
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    Yes, relational database design would tell you to create an intersection entity for users attending concerts.

    ie.

    person: person_id (pk), forename, surname etc. etc.
    concert: concert_id (pk), venue_id (fk), concert_date, band_name etc. etc.
    venue: venue_id (pk), venue_name, town_id (fk) etc. etc.
    town: town_id (pk), town_name, os_latitude, os_longitude etc. etc.

    ..and..

    concert_booking: booking_id (pk), concert_id (fk), person_Id (fk), attended

    When somebody registers for the site a record is created in the person table. When somebody buys a ticket for a concert a record is created in the concert_booking table, and when somebody attends that record is updated with the fact that this has been the case.

    Therefore if you wished to list all the people who bought tickets for Hawkwind on 21st January 2003 in Vancouver you'd write...

    SELECT p.forename, p.surname
    FROM person p, concert_booking b, town t, venue v, concert c
    WHERE c.bandname = 'Hawkwind'
    AND c.concert_date = '21012003'
    AND v.venue_id = c.venue_id
    AND t.town_id = v.town_id
    AND t.town_name= 'Vancouver'
    AND b.concert_id = c.concert_id
    AND p.person_id = b.person_id


    ...to list those who attended you'd simply add the line...

    AND b.attended = 'Y'

    ...similarly to see all the concerts that Fred Smith attended, with their date, venue and town...

    SELECT c.bandname, c.concert_date, v.venue_name, t.town_name
    FROM town t, venue v, concert c, concert_booking b, person p
    WHERE p.forename = 'Fred'
    AND p.surname = 'Smith'
    AND b.person_id = p.person_id
    AND c.concert_Id = b.concert_id
    AND b.attended = 'Y'
    AND v.venue_id = c.venue_id
    AND t.town_id = v.town_id


    Sean

  8. #7
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Very good ideas. I'll try implementing them today.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  9. #8
    d31
    d31 is offline
    Member
    Join Date
    Oct 2003
    Location
    Sydney, Australia
    Posts
    39
    Member #
    3423
    Let us now how you get on - I'm interested

  10. #9
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,657
    Member #
    1234
    Liked
    137 times
    Here's a similar question. I'm working on a DB akin to this, where it's a many to many split up with a junction, but I'm having trouble making a schema that makes it really easy to add info to it. It'll need to be very easy, since the DB's going to have 1000's of entries.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  11. #10
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    seanmiller's post looks good, but along the lines of the question, you will probably want to do something like so:
    Code:
    CREATE TABLE concerts (
        id int NOT NULL AUTO_INCREMENT,
        performer_id int NOT NULL REFERENCES performers(id),
        venue_id int NOT NULL REFERENCES venues(id),
        scheduled datetime NOT NULL,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE users (
        id int NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE concert_activities (
        id int NOT NULL,
        description varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO activities (1, 'Attendance Desired');
    INSERT INTO activities (2, 'Purchased Ticket');
    INSERT INTO activities (3, 'Attended');
    INSERT INTO activities (4, 'Scalped Tickets');
    
    CREATE TABLE concert_activity_pipeline (
        id int NOT NULL,
        user_id int NOT NULL REFERENCES users(id),
        concert_id int NOT NULL REFERENCES concerts(id),
        concert_activity_id int NOT NULL REFERENCES concert_activities(id),
        created datetime NOT NULL,
        PRIMARY KEY (id)
    );
    Now, every time someone does something, you insert it into your pipeline by USER, CONCERT, ACTIVITY, and DATETIME. Although the table will expand greatly in length, what you're inserting is many many many integers, which are the quickest datatype in MySQL (and most other databases, too). If you index your table properly, you will be able to store millions upon millions of rows in this table without speed issues. Also, because it's a PIPELINE table type, it will only be used for INSERTS and SELECTS, which means that the absense of rowlocking UPDATE and DELETE statements will increase the speed of the table that much more.

    Finally, making everything third normal form will allow you to add activities, venues, concerts, all without any impact to your architecture, because everything is controlled with metadata.


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 09:26 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com