In creating data models designs you must frequently decide the way in which you want to store data of the same kind.
For instance, if you have to store multiple telephone numbers or emails or if you have to keep different names stored in your database system that for its nature belong to different tables (e.g.: client name, provider name, spouse name, children name, inspector name, etc.) you usually tend to save them as different attributes of these tables.
If for any reason you need to store not one, two or three but as many as needed for these kind of data you usually create a second table as to keep track of all the different records that will be added as part of the business logic for any parent table.
While I was working for CGI Canada in developing the “Web Inspection and Evaluation System (WIES)” I found a workaround to solve this problem or at least until now I have found that my so called “invention” could be considered worthwhile regarding this matter.
I created a concept called “Island and Satellite Entities”.
By the way, my name is Carlos Porras and I am from El Salvador and I am very pleased to have you here sharing these concepts with me.
These islands are pivotal tables of information in your design. Satellite records rely on having a parent record on the pivot table.
To make its usage a little more clear let’s suppose you want to control an inventory system in which you want to store as many different telephone numbers, emails, fax numbers and contact names for a single customer, for a single provider, for a single financial institution that you have registered, for every single vendor and customer service person, etc.
You will probably want to avoid cluttering your database with a growing number of detail tables, writing code to be reusing the same class (OOP) that manages your business rules, etc. All of this seems to be absolutely redundant.
Instead of creating a lot of different tables with these attributes you must better create tables for each kind of attribute.
In using this approach, all tables points to a single pivot entity which registers the kind of attribute that you want to store. If there is a match with your search criteria it will guide you to every single record belonging to the kind of attribute(s) that had been stored in your database previously and that you are searching for.
For instance, if I have a “customers table” and an “island table” I just have to create a new record in the island table for every new entry that I want to store in the correspondent “satellite table” (meaning tables whose names belong to the kind of attribute to be stored: telephone table, email table, names table, address table, etc.)
Island Table is in purple (I have given the name “ATTRIBUTES” to that table)
Satellite Tables are also in violet color pending of the pivotal Island Table (I HAVE CALLED THEM “ADDRESS”, “EMAIL”, “TELEPHONE”, and “NAME”)
The calling entities are colored in yellow: customer, salesperson, etc.
Hope this will help you in your designs too.
It must be said that I had a partner in doing this. He is a French-Canadian guy, a real one, descendant from original indian american tribes, who worked hard in writing all Use Cases (UML Modelling) for that system. He is well known among his friends as F^3 (F Cube), cute, charming, famous for his adventures on his snow jet sky as a park guard, good father and hard working man. He also had to play the rol of “negotiatior” between me and a bunch of Russian-Chinesse guys who where hired as outsourcing for building the system, specially a lady whose last name was Valentina, which used to argue frequently with me …. SPECIALLY BECAUSE IT WAS TRULY HARD FOR THEM PROGRAMMING THIS KIND OF DESIGN METHODOLOGY as they were used to the “Old School” approach (they had chosen Oracle Forms with no composite foreign keys) for building this instead of the Oracle XSQL/XML/XSLT with full entity referential integrity strategy that I had originally chosen for doing this.
I have also to mention Mr. Vito de Simini who played also a very fundamental role in supporting the conception of this idea and its former implementation. He was in love with the idea of generating configurable reporting on the fly without any programming effort but customizing parametrizable data who will define the order and logic under which any further repot could be built in future time.