4 Replies Latest reply on Jun 8, 2010 12:42 PM by fitch

    Table Setup



      Table Setup


      I am creating a database for an art gallery. The people I will be tracking in my database will be Artists, Potential Artists and Clients. I will be holding different types of data on each of these but some data will be common to all such as contact details, account created etc.


      I see it as I have two options:-



      (i) Create three seperate tables one for artists, one for potential artists and one for clients.



      (ii) Create a Contacts table that will hold clients, artists and potential artists. I would have three table occurences of the contacts table in my relationships graph, one for artists, one for potential artists, one for clients. They would be dertimined by their type. If they are an artist an ArtistID would be generated for them, if they are a client a ClientID would be generated for them using their ContactID. I would then have a three other tables that would hold data specific to their contact type e.g studio for artists or delivery address for clients creating a relationship using their generated ArtistID and ClientID.



      Would this second option be feasible and what would be the benifits?



      Thanks in advance





        • 1. Re: Table Setup

          You could have a person table and then a status field

          Then you could keep client, potential artists and artist data in separate tables 

          You could keep contact information in a contact table regardless of status

          You can put these different pieces of information on different tabs of a tab control tool

          Or on separate portals or even just a group of related fields on the layout.


          The possibilities are endless. 

          You can use relationships as you suggested as well but that can get confusing because its not readily apparent how the relationships are being filtered when looking at the database.


          • 2. Re: Table Setup

            Definitely keep people in a People table ("Contacts" or whatever) and build from there. (Aren't we all potential artists?)


            That doesn't automatically mean you need a TO on your graph for each type of Contact. It might, but not necessarily. You might, for example, want a different layout for each type. Or perhaps just a different tab on the same layout.


            You may want to take the same approach with address information, i.e. keep all addresses in one table and use one or more fields to designate the type of address (studio, delivery, etc).

            • 3. Re: Table Setup

              Sorry for the late response; Ive been away for a while.


              Nevertheless, back to the task in hand.


              Thanks for your replies. I've opted to use this model which apperently is the supertype/subtype model.


              Thanks for pointing out that I don't necessarilly require TO's of every type of 'Contact.' That poses a question though. When would it be benificial to have  a TO of 'Contact' that would segregate 'Artists' from 'Clients' in my scenario? e.g If I had a table containing artworks, would I create a realationship to a TO of 'Contacts' named 'Artists' or just link that to the 'Contacts' table.


              Again thanks in advance, your help is greatly appreciated.





              • 4. Re: Table Setup

                One solution might be that the artworks table has an Artist ID and a Buyer ID.


                You'd link the artist from contacts using Contact ID -< Artist ID.

                You'd link the buyer from contacts using Contact ID -< Buyer ID.


                You might have two TOs of artworks linked this way to a contacts TO.

                And/or you might have two TOs of contacts linked this way to an artworks TO.


                Depends how you need to view it, from one perspective or the other.