Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2016
    Posts
    1

    Help with the structure of a DB

    I've been working on a website that is going to provide information of a certain bands world tour they did back in the good old days.

    I am a web developer myself and I do know a bit about databases (mostly MySQL), but it's really not my strongest point. Especially when it comes down to designing the structure of the DB.

    What I have now is an Excel sheet with thousands of rows, each row containing information of one particular song played in one particular show. The row includes information like this:

    1. ID
    2. Song name
    3. Song number
    4. Date (of the show)
    5. Country (where the show was)
    6. State
    7. City
    8. Venue

    Now, this Excel sheet has not been collected to be imported to a DB, so I'm open to any suggestions with this project.

    The initial goal is to present different kinds of details on the site, based on the information in the DB, for example:

    - Most played songs
    - A list of songs sorted by play count
    - One row would include: song name, play count, first played (date+city), last played

    - List of shows
    - A listing of all of the shows
    - Each show is linked to its own page (see below)

    - Page for a show
    - Contains info of one particular show
    - Has to include the setlist (list of songs played at the show)
    - Other info too, of course

    And that's just the beginning, I'm thinking it would be cool to be able to present pretty much any statistical information... like "the most played songs in California in 1993" or "the most used venue in USA".

    I know how to do all this frontend, but it's the structure of the DB I'm struggling with, 'cause I'd like to make it as efficient, good and versatile as possible.

    Any suggestions or help?

    If I'm not being clear enough with my intensions or with what I have, just point it out and I'll try to explain it better, thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    design your schema. whether you use a relational (SQL based) db such as MySQL or similar or a non SQL db is up to you.

    if you do use a SQL db then normalise the design

    the import process is relatively trivial.

    import the whole db as a CSV
    then populate the tables using INSERT INTO queries in order of precedence (eg countries, then states/provinces/counties, then cities, then venues.

    use select distinct to identify unique values for each table

    clean the data (fix duplicates, mispellings and so on)

    repeat data cleaning until such time as you have 'clean' data. its the peril of using a spreadsheet for data entry. CSV is fine as a transport medium, spreadsheets are fine of allwoing usiers to vidualise their data as they see fit, but they are not a good solution for data storage
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2016
    Posts
    89
    Hi Co.

    For modeling correctly a db (See example on this dbforums post
    here),



    1. Build a conceptual data model (CDM). The Logical data model is very simple then to generate. With tools, the LDM is automatically built as well as the SQL script for creating the DB
    2. Use dedicated tools which build CDM not only LDM like :
      1. Power*AMC (excellent but expensive)
      2. JMERISE, excellent freeware here



    And of course apply at least the 3 first Normal forms (NF).

Posting Permissions

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