6 Replies Latest reply on May 5, 2010 12:20 PM by silba2021

    Some help figuring out how stucture my database

    silba2021

      Title

      Some help figuring out how stucture my database

      Post

      Hi, I am newcomer to the whole database thing, so please bear with me. I have downloaded the trial version of Filemaker Pro 11 for the mac and have spent the last few days learning the basics of creating a database. Now I want to apply that to a database I want to create to suit my particular needs.

      We are a team of 3D Designers creating 3D models and renders. We have Developers coming to us with Product concepts which we Model, Sculpt, Texture and Render for them based on their requirements. Each Product can involve any or all of those tasks and we could have one or more 3D Designers assigned to each of those tasks.

      I need help in putting all that information in some kind of structure/relationship. I would appreciate some help in getting me started, thanks in advance

        • 1. Re: Some help figuring out how stucture my database
          mrvodka

          I might start off with the following tables:

          Project

          Task ( Type )

          People ( Designer Information )

          Assignment ( Join table assigning Deisgner to one or many tasks )

           

           

           

          Project --< Task --< Assignment >-- People

           

          • 2. Re: Some help figuring out how stucture my database
            aammondd

            The first thing I suggest is acutally get yourself a bunch of 3x5 cards and some highlighters (2 colors should do) and start listing on the card information that is unique to a single particular type of record.

             

            Then on each card list a field that is that records key ID and highlight it  and number the card

            Start with the most central type of record you can  think of (In this case product)

            Now look at the remaining cards and see if any data can be tied to this record directly

            If so on that other card add the key field of this record and highlight it (in the second color and note the card number it comes from)

             

            By now you should understand parent child relationships this method will help you sort out as well as document the structure of your database.

             

            From what you listed you I can see the following tables for sure

             

            Designers

            Developers

            Product (Project)

            Product Tasks

            Task Assignments

             

            You will have to fill in more detail about what kind of info you want to capture on each table but if you use the card method you will be able to structure your data such that you can form the proper relationships then make use of FM layouts and tools to display and track your data in the appropriate way.

             

             

             

            • 3. Re: Some help figuring out how stucture my database
              aammondd

              Designers and Developers could all be in a people table as suggested above with a classification but it also may make things easier if you want to record different information about those types of people to put them in separate  tables.

              • 4. Re: Some help figuring out how stucture my database
                silba2021

                wow! this forum is really busy, I thought my thread had been deleted when I couldn't find it on the first page.

                 

                mr_vodka, aammondd, really appreciate your input.

                 

                Using the card method here is what I came up with. I know it is not complete but I can't seem to pin down certain parts i.e. the #4 card, which where I need to figure out when the two stages of the task are due based on the tasks that are involved. I got the calculation part figured out I am just not sure how the Assignments and the Designers card fits in with the rest. I am not even sure what could be the primary key in #4.

                Thank you for your suggestions.

                ER_Diagram

                • 5. Re: Some help figuring out how stucture my database
                  aammondd

                  You need a task ID In the product tasks 

                  Then instead of 4 different  task fields just 1 and assign it a type

                   

                  (You can create a script that will make 4 records 1 for each type whenever you create a new product. There are other methods of handling this to it really depends on what process you want to follow)

                   

                  Then you can  make assignments to each task id with product ID and task ID as keys.

                  You can then place completion dates or other tracking information in the assignments You can also compare Tasks for the same product and the related completion if there is a priority to the tasks. Maybe you dont want to assign a particular task till some other has been completed. So you could place the tasks in a hold status waiting for others to complete.

                   

                  Designers than can see their open/overdue etc assignments or Grab pending tasks it really depends on how your shop functions.

                   

                  The designers table doesn't really need product id because it will be related to him through their assignments.  You would want developers name(ID) on the product rather than  the product ID on the developer the relationship is probably 1 developer to many products (Sorry if I was confusing on that earlier its easier to see this way)  if you have many developers per product then you need a product-developers table with both keys in it.  Im assuming that Developers are responsible for the spec of the product and designers are responsible for the actual building

                   

                  • 6. Re: Some help figuring out how stucture my database
                    silba2021

                    Yes you are right, a developer can have many products but each product has only one developer. Each designer can have many tasks related to a product and each product can have many Designers working on it. I am guessing this is why we have an Assignment table between the Designer and the Task.

                    I am going to read your reply once more when I get home this evening and try to understand it better. Once again thanks for taking the time.