Announcement

Collapse
No announcement yet.

Ive created my SQL Tables - now Im trying to link them

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Ive created my SQL Tables - now Im trying to link them

    Hi,
    Im on a steady learning curve with mysql courtesy of gforceindustries and I have come across my first stumbling block

    I have created 4 tables and added some sample data. I would like to link the tables/sample data - which I guess in turn will be the building blocks of my database. I currently have 4 tables:

    stock_dateadded;stock_description;sotck_id;stock_p roject_number I would like to be able to link these together and then I can start on my PHP interface for data entry.

    Can anyone help?

    Muchus cheerios!

  • #2
    Re: Ive created my SQL Tables - now Im trying to link them

    Linking is generally done at the query level, or in a view. For data entry you will need a script to populate both tables (that are related) or enter data into only one table.
    "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

    Comment


    • #3
      Re: Ive created my SQL Tables - now Im trying to link them

      I just reread your question, if you are using PHP, your code would drop the related data into the correct table. PHP is responsible to do this, not mysql.
      "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

      Comment


      • #4
        Re: Ive created my SQL Tables - now Im trying to link them

        Download and install phpMyAdmin at once! Gives you overview of dB architecture and how stuff "goes together". You run it from Apache web server, or IIS, if you have php installed. It really helps to have a visual tool when your learning. The MySQL visual design tools are also cool.

        Comment


        • #5
          Re: Ive created my SQL Tables - now Im trying to link them

          You would usually establish some links when you create your tables (you can modify existing tables to add them), by stipulating that attribute A from table X is a foreign key linked to attribute B from table Y.

          It's also standard to create a single table storing the bulk of information about a given thing. For instance, your four tables (are you sure you mean tables, rather than attributes within a table?) would be the four attributes of the stock table, with stock_id being the primary key. stock_project_number would be a foreign key pointing to the project_id attribute in the project table.

          Basically, you need more than one attribute in a table if you want to link them together usefully. The primary key gives you a unique reference for each entry within a table so you can reference a specific row and access all of the other data held in that row.

          I'll post the SQL syntax shortly to create these two tables, link them together and populate with some sample data and annotate with some notes on what each part does - am just about to head into work though (lame).
          Gareth Howells

          BSc (Hons), MBCS, MCP, MCDST, ICCE

          Any advice is given in good faith and without warranty.

          Please give reputation points if somebody has helped you.

          "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

          "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

          Comment


          • #6
            Re: Ive created my SQL Tables - now Im trying to link them

            Thanks guys.... Initially thought I would get shot down for asking such a basic question - appreciate the help.

            Will have a proper go with it when I get the chance - thanks again

            Comment


            • #7
              Re: Ive created my SQL Tables - now Im trying to link them

              Sorry for the delay, very busy today. Below is an excerpt of an SQL script to create a stock table and a project table.

              I've linked the two by referencing the project table in the stock table.

              Questions for you:

              1) Why have I done this? What does this now allow me to do?
              2) Why have I done this rather than referencing the stock table in the project table? What would that have allowed me to do instead?
              3) What is a potential drawback to this method? Can you think of a way around this?

              Bonus points if you can answer #3 without duplicating data. Post back your answers or if you're in need of a hint

              As for getting shot down, we shoot down people who post inane questions that they've clearly not made any attempt to answer by themselves. You've shown some initiative and had a go before asking for help. Nobody knows all the answers, but you learn a whole lot more by trying it for yourself before getting someone else to tell you how to do it

              Code:
              CREATE TABLE project
              (
              projectID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
              projectName VARCHAR(40) NOT NULL,
              PRIMARY KEY (projectID)
              );
              
              CREATE TABLE stock
              (
              stockID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
              stockDescription VARCHAR(100) NOT NULL,
              stockProject SMALLINT UNSIGNED NOT NULL,
              PRIMARY KEY (stockID),
              CONSTRAINT projectFK FOREIGN KEY (stockProject) REFERENCES project(projectName)
              );
              Last edited by gforceindustries; 7th April 2011, 22:10.
              Gareth Howells

              BSc (Hons), MBCS, MCP, MCDST, ICCE

              Any advice is given in good faith and without warranty.

              Please give reputation points if somebody has helped you.

              "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

              "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

              Comment


              • #8
                Re: Ive created my SQL Tables - now Im trying to link them

                Nice one matey thanks!! So in answer to your questions - Im thinking. You have linked them by this reference as you can assign multiple stock items to one project but you wouldnt assign multiple projects to one item.

                For #3 I think I need a clue - I am thinking its something to do with referencing the same value more than once or something????

                Comment


                • #9
                  Re: Ive created my SQL Tables - now Im trying to link them

                  Yes. For #3, what I'm looking for is for you to tell me how I can assign one SKU to multiple projects as well as one project incorporating multiple SKUs.

                  The limitation to the existing design is that every time a new project comes up that uses a part you've had in a previous project, you have to add that stock item to the database again. Have a read up about database normalisation - we don't like duplication

                  Abstract the data out into groups. We need data about stock. We need data about projects. And we need data about the link between stock and projects.
                  Gareth Howells

                  BSc (Hons), MBCS, MCP, MCDST, ICCE

                  Any advice is given in good faith and without warranty.

                  Please give reputation points if somebody has helped you.

                  "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

                  "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

                  Comment


                  • #10
                    Re: Ive created my SQL Tables - now Im trying to link them

                    gforceindustries-
                    While you are correct in your instruction to 5habbaranks, I have to disagree.
                    Using constraints is very risky when the table will called via php. The issues come up when you add data that is out of bounds. The DB will output an error but that will not be presented back to the user interface (the web, etc...)
                    Its generally best to allow the DB the least constrictive rules, and build the error checking into the PHP app.

                    You can possibly return all DB errors back out to the web interface, but thats generally not a good idea for security.

                    In reality constraints are used when you have multiple developers working on an app in the same DB, when they misbehave the DB will keep them in check The app should implement (or duplicate) the constraints of the data. Then in production the DB constrains are removed. There is nothing more frustrating than executing an action and nothing happening. No new data in the DB and no error.

                    Anyway just my two cents....
                    Feel free to disagree, as there is no universal correct way.

                    further reading....
                    http://dev.mysql.com/doc/refman/5.0/...alid-data.html
                    http://www.ytechie.com/2008/05/when-...nstraints.html
                    "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

                    Comment


                    • #11
                      Re: Ive created my SQL Tables - now Im trying to link them

                      Main reason for using them here is that it's another hint towards considering the links between tables when designing the database schema.

                      As for production apps... I leave the constraints on the database anyway. As you say, a properly written application should adhere to these requirements anyway so there won't (shouldn't :mrgreen) be a problem. Plus if a DB problem does occur, I log it.

                      As with anything in IT though, we could debate it for hours over , and
                      Gareth Howells

                      BSc (Hons), MBCS, MCP, MCDST, ICCE

                      Any advice is given in good faith and without warranty.

                      Please give reputation points if somebody has helped you.

                      "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

                      "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

                      Comment

                      Working...
                      X