11 Replies Latest reply on Oct 22, 2015 5:24 PM by bigtom

    B2B relationship database

    dannygrio

      Dear all,

       

      I am trying to create a simple and efficient database to manage relationship with other companies. In this case hotels!

       

      Therefore, I would like to know, how I can start a filemaker database to manage the following:

       

      - I have 100 contacts that I work with = 100 Hotels and they are all protect clients

       

      - Each Hotel has a value given by me of their importance to my business = I can call this Number of Stars

       

      - I need to create information about the hotels, like number of rooms, average room value,  promotion, benefit give, location, amenities, check in time and check out time, if they ar remember of an association etc..

       

      - I would also like to file each Hotel with an image or more images of the properties if I can

       

      - A field for extra observation etc.

       

      - And key staff contact details, ex: general Manager, Owner, Reception contact etc.

       

      - A field for notes for each contact.

       

      I understand that maybe creating this is simple, but I am a bit confused, since most people deal with contacts of people only and write the company they work in. In this case, I am curating a number of companies that I think are my clients and in each company I should have staff contacts. Nevertheless, the details that I want to manage are of these companies. Rooms, location, price etc..

       

      Any tutorial or any guidance for this?

        • 1. Re: B2B relationship database
          bigtom

          Just looking at what you described, beside relating some of the amenities, images, contacts, or other info, it looks like a pretty flat database. I am not sure where the needed relationships would be unless you expect the contacts will be moving around from hotel to hotel in short periods of time.

           

          If I were you I would start with a base table (hotels or properties), contacts table, and an images table. Then break out more relationships as you need them.

           

          The base table will hold most all of your information. To get started create a layout you like first and plug in all the fields you need. Lots of little tutorials around.

          • 2. Re: B2B relationship database
            dannygrio

            Thank you bigton!

             

            I am learning, so I believe fro the info you gave me i should create:

             

            - One database for properties

            - One for contacts

            - Other for images

             

            then do the relationships!!

             

            Is this correct?

             

            Thank you

            • 3. Re: B2B relationship database
              coherentkris

              "database" and "table" are not the same thing.

              You should start out by examining the entities and how they relate to each other.

              This analysis will reveal the entities that should become tables and relationships between them.

              Can a person ( i hesitate to call them contacts because "contact" usually means type of person ) have many roles at a hostel?

              Can a person be affiliated with many hostels?

              How many images can a hostel have? (this is cardinality of the relationship between entities)

               

              You might have a need for tables PERSONS, PROPERTIES, PERSONS PROPERTIES ROLES (join table), IMAGES, and ROLES

              • 4. Re: B2B relationship database
                dannygrio

                Hi,

                 

                Now I am understanding more.

                 

                basically, I want something that I can manage different contacts (companies) and each company have a staff contact or two.

                 

                a example out of the box: a database of cars

                 

                - I would create the database of cars

                . Images of the cars

                . how many doors

                . Electric or Fuel

                . 0-100 in how many sec

                - Engine

                . Location of that car

                . Importance of the car for me (is it the best car between all in the database?, is it a 5 star car? etc)

                 

                - I would also create a database for the owner and mechanic of each car

                . contact info, phone number etc

                . observation notes

                etc.

                 

                This is just an example in other terms, but I want to do this with Hotels and contact of each hotel. I also want to have information about each hotel and its importance for me. Moreover, i will have the contact of each person responsible for the hotel and any other staff if I have more contacts for one property. All in all, is a database for me to manage the properties I curate!

                 

                Another good example of a similar database, should be one of someone that sells properties. They need to list every property they have for sale and images, details, how many rooms, location etc.. In the same database, they need to write dow each owner for each property so they can link buyer with property owners. 

                 

                My database will be the same, but for hotels and answering the questions above:.

                 

                Can a person ( i hesitate to call them contacts because "contact" usually means type of person ) have many roles at a hostel?

                 

                usually is my contact in that hotel, i.e.: Director of Marketing / Sales (one person)

                but, I might have to also speak with a Press and PR contact and therefore need another contact for that hotel. In addition, this same contact can be the contact of other hotels if it is a chain hotel and I am speaking with the Global Director. Nevertheless, it can come out like this: Global Director of 3 hotels. Each hotel from this list of 3 properties also have a Director to run each property. Hope I was clear.

                 

                Can a person be affiliated with many hostels?

                 

                I guess is the answer above as well. yes they can! Lets say Marriot in New York has a director. But I have Marriot in NYC, Miami and Las Vegas. They each have a person responsible and a head of sales for all 3 of them!

                 

                How many images can a hostel have? (this is cardinality of the relationship between entities)

                 

                I will probably use 5 or 10 images for each! Is just images of the properties so I can relate to it when managing over 200 properties. Use the example of the cars.. if the database was about cars, I just want to know what it looks like the car, the interior of the car etc.. The property database is the same! What it looks like, the room? etc.

                 

                I believe is more clear now! right?

                 

                Many Thanks

                • 5. Re: B2B relationship database
                  coherentkris

                  What you wrote suggests that you need tables PERSONS, PROPERTIES, PERSONS PROPERTIES ROLES (join table), IMAGES, and ROLES


                  Use whatever naming convention works for you...


                  ROLES::pk_ROLE_ID

                  PERSONS::pk_PERSON_ID

                  PROPERTIES::pk_PROPERTY_ID

                  IMAGES::pk_IMAGE_ID

                  IMAGES::fk_PROPERTY_ID

                  PERSONS PROPERTIES ROLES::pk_ID

                  PERSONS PROPERTIES ROLES::fk_PROPERTY_ID

                  PERSONS PROPERTIES ROLES::fk_PERSON_ID

                  PERSONS PROPERTIES ROLES::fk_ROLE_ID


                  Make the appropriate joins

                  • 6. Re: B2B relationship database
                    dannygrio

                    Thank you I will try to start something!

                     

                    Thank you once again!

                    • 7. Re: B2B relationship database
                      bigtom

                      This can have a bunch of tables if you want it to, but take time to decide if you need a whole table with a relationship or just a custom value list. Again, it is easy to break these out into a table later if you decide to.

                       

                      Please consider:

                      Property->Roles->Person

                      and

                      Property->Images

                      to start.

                       

                      You may end up with Property->Rooms->Amenities as well

                      • 8. Re: B2B relationship database
                        bigtom

                        I am not seeing why you would need a special join table as everything can be related with the original tables.

                        • 9. Re: B2B relationship database
                          dannygrio

                          Thanks! I am still learning and most of these things are new for me, so I am struggling on the associations, names and terms used here. I will keep this and I am studying so after I finish, hope all makes sense. I just want a property database with some contacts involves if I have to call each property/hotel! The database will be about the hotel and what they offer, plus what rank they have in my business. Basically this is it! bigtom

                          • 10. Re: B2B relationship database
                            bigtom

                            This is all you really need.


                            ROLES::pk_ROLE_ID

                            PERSONS::pk_PERSON_ID

                            PROPERTIES::pk_PROPERTY_ID

                            IMAGES::pk_IMAGE_ID

                            IMAGES::fk_PROPERTY_ID

                            • 11. Re: B2B relationship database
                              bigtom

                              Fairly easy. I know you already marked an answer as correct but I figure I would throw this out to help you along. You can swap Persons and Roles in the graph, but this makes more sense to me.

                               

                              Sample file did not take long at all.

                               

                              Screen Shot 2015-10-23 at 8.53.01 AM.png