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 7 of 7
  1. #1
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    326
    Thanks
    42
    Thanked 2 Times in 2 Posts

    Am I going about making database right?

    Hi!

    This is my first MySql project. It's going to be a bit of a learning curve for me but I'm going to take a good stab at it I feel I can use this to my advantage!

    Here goes! Right now I just have this flat file that I use php to pick apart and exception process it. It works but it was interesting getting it going.

    U15 Boys FT Rumble '98||Photo||team.jpg
    U15 Boys FT Rumble '98||Mgmt0||Head Coach||Mickey Mouse||abcdefg@comcast.net||123-456-7890||
    U15 Boys FT Rumble '98||Mgmt1||Assistant Coach||Minnie Mouse||abcdefg@yahoo.com||098-765-4321||
    U15 Boys FT Rumble '98||Mgmt2||Team Manager||Donald Duck||abcdefg@comcast.net||555-212-1212||
    U15 Boys FT Rumble '98||Achievements0||Team History0||2013 Spring – ISL Boys U15 2nd White Division (2nd Place of 5)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2012 Spring - CIYSL Boys U14 1st Division (1st Place of 5)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2011 Fall - CIYSL Boys U14 1st Division (5th Place of 6)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2011 Spring - CIYSL Boys U13 1st Division (6th Place of 8)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2010 Fall - CIYSL Boys U13 1st Division (4th Place of 6)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2010 Spring - CIYSL Boys U12 2nd Red Division (3rd Place of 9)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2009 Fall - CIYSL Boys U12 2nd Red Division (5th Place of 9)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2009 Spring - CIYSL Boys U11 2nd Blue Division (2nd Place of 8)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2008 Fall - CIYSL Boys U11 2nd Blue Division (4th Place of 6)
    U15 Boys FT Rumble '98||Achievements1||Team History1||2008 Spring - CIYSL Boys U10 A Blue Division
    U15 Boys FT Rumble '98||Achievements1||Team History2||2007 Fall - CIYSL Boys U10 BC Division
    U15 Boys FT Rumble '98||Achievements0||Tournaments0||2013 Midwest Soccer Classic U15 Boys Finalist
    U15 Boys FT Rumble '98||Achievements1||Tournaments0||2013 Siege at St. Francis U15-U17 Silver Champions (3 wins / 1 tie)
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2012 SCSA Classic at the Rock U14 Boys Finalist (3 wins / 1 loss)
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2011 SCSA Nightmare at the Rock U14 Boys Finalist
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2011 Westside United Invitational U14 Boys Champions (3 wins / 1 tie)
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2010 SCSA Nightmare at the Rock U13 Boys Finalist
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2010 A.L. Smith Invitational U13 Boys (1 win / 1 tie / 2 losses)
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2010 Pike Fest Boys U13 Bronze (1 win / 2 losses)
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2010 Siege at St. Francis U12 Boys Gold (0 wins / 3 losses)
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2009 Kick It 3v3 Indy Boys U11
    U15 Boys FT Rumble '98||Achievements1||Tournaments1||2008 Fisher's Halloween Classic U11
    U15 Boys FT Rumble '98||Achievements2||Tournaments2||2008 Siege at St. Francis U10 Boys White - Participant Award (1 win / 2 losses)
    U15 Boys FT Rumble '98||Achievements2||End||
    Right now I have a table called teams which has four columns. Key, age-group (U12), sex and team-name. In another table named head-coach I have team-name, hc-name, email and phone.

    In teams key is my primary key and team-name is indexed. In head-coach team-name is indexed.

    I wanted to run a short query on one team but really are unsure of the join syntax. Access has spoiled me. It was nice being able to link the common field for a join.

    So am I going at it right? And if you could point out that query syntax for a join I'd really appreciate it!

    Regards,

    Dave
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs

  • #2
    Regular Coder Linux_Sage's Avatar
    Join Date
    Mar 2014
    Location
    Sterling,VA
    Posts
    106
    Thanks
    0
    Thanked 10 Times in 10 Posts
    Seems OK so far. Except in the head-coach table I would use an int as a primary key instead of the team-name, and then in your teams table I'd create a new column that would be a foreign key (name it coach_id or something) that points to this head-coach record. That way you'd be able to create a contraint that prevents data from being 'orphaned'.

    Then you could do something like
    Code:
    SELECT * FROM teams t
    INNER JOIN head-coach h ON t.coach_id = h.coach_id

  • #3
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    326
    Thanks
    42
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Linux_Sage View Post
    Seems OK so far. Except in the head-coach table I would use an int as a primary key instead of the team-name, and then in your teams table I'd create a new column that would be a foreign key (name it coach_id or something) that points to this head-coach record. That way you'd be able to create a contraint that prevents data from being 'orphaned'.

    Then you could do something like
    Code:
    SELECT * FROM teams t
    INNER JOIN head-coach h ON t.coach_id = h.coach_id
    Ok, I got that understood it. I'm kind of fuzzy on the foreign key thing.

    And when you say "teams t and head-coach h' what do the t and h represent? I'm getting its like a variable.

    This is a whole different animal from Unisys Data Management System II (DMSII) mainframe database. That's where I'm good with primary-key and key and understanding the join it's just learning to match my apples with oranges so everything tastes the same! =)

    The more examples I see and the more on hands I do the better I'll become. So I was right in breaking it down into common itemized tables then.

    Thanks for your help! I'll most likely be revisiting this thread to ask more trivial questions!
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs

  • #4
    Regular Coder Linux_Sage's Avatar
    Join Date
    Mar 2014
    Location
    Sterling,VA
    Posts
    106
    Thanks
    0
    Thanked 10 Times in 10 Posts
    The h and t are aliases. They let you refer to the tables in short hand.

    You should look into foreign keys, indexes and other things especially subjects related to Data Normalization. They help you maintain the integrity of the database. This tutorial here is a nice introduction to that topic: Relational Database Design - Normalization - Database Tutorials | Dream.In.Code

  • Users who have thanked Linux_Sage for this post:

    MrBiggZ (04-04-2014)

  • #5
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    326
    Thanks
    42
    Thanked 2 Times in 2 Posts
    Ah ok I got it!

    So if you had a table named 'places' and a column named 'city' you would say something like 'places p' and then refer to that column as 'p.city'

    Tricky part is once I get the data into the database manipulating it. Like I may have a team that the head coach, team manager and asst coach all change there is there no reason why I can't update those three fields at once? Granted that's a bit off in the far distance.

    Oh .. never tried it but are images (png,jpg,gif ... etc.) storable in a database?

    I'm on a roll now!

    Thanks again!
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs

  • #6
    Regular Coder Linux_Sage's Avatar
    Join Date
    Mar 2014
    Location
    Sterling,VA
    Posts
    106
    Thanks
    0
    Thanked 10 Times in 10 Posts
    Yeah, that's about it. You can name your aliases to whatever and use that to reference columns.

    Yeah the database tables need to be engineered and designed in such a way that you can handle all those types of scenarios. I'm not sure if this would be the best way to approach it as I'm just throwing it out there but since you're describing different types of managers you could set it up like:

    Team table with: team_id, team_name, age_group, sex, management_id
    Management table with: manage_id, role_id, team_id, name, email, etc.... (other identification fields)
    Role table with: role_id, manage_id, role_name

    This setup will have the Team table have a manage_id that points to the list of people who manage said team and in this table there will be a role_id that specifies who they are (head coach, team manager, assistant coach?)
    And then the Role table will define what that role is so in the future you could perhaps add more while hopefully not impacting the system too much. Anyway, hopefully I did that right.

    As for images, technically you could store them into the database directly as BLOBs, but I found it to be easier to simply store the path of the image from your filesystem instead. Especially if you're working on a web-based system and the path is basically all you need anyway.

  • #7
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    326
    Thanks
    42
    Thanked 2 Times in 2 Posts
    I'm making progress! I think!

    Perhaps you could take a look at what I got going. I was using the build query function in phpmyadmin but when I ran the query I didn't get what I had expected. Not at all.

    I can PM you and tell you how to get at the database.
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs


  •  

    Posting Permissions

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