Thread: Help with the structure of a DB
02-09-16, 08:13 #1Registered User
- Join Date
- Feb 2016
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:
2. Song name
3. Song number
4. Date (of the show)
5. Country (where the show was)
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!
02-09-16, 08:22 #2Jaded Developer
- Join Date
- Nov 2004
- out on a limb
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 storageI'd rather be riding on the Tiger 800 or the Norton
06-28-16, 08:20 #3Registered User
- Join Date
- May 2016
For modeling correctly a db (See example on this dbforums post here),
- 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
- Use dedicated tools which build CDM not only LDM like :
- Power*AMC (excellent but expensive)
- JMERISE, excellent freeware here
And of course apply at least the 3 first Normal forms (NF).