Relational database structure – Handling Types

Topic

New Reply

This issue has always proven to be a challenge to me when designing a relational databases: Types

For example: You are entering in data for a person: Name Address, etc. Each Person is of a Certain “Type” (‘Friend’ or ‘Co-Worker’ for example). In this case, to keep it simple, each person can only be of a single type. A Friend type might have: hobbies, interests, birthdate, wedding anniversary, etc. while a Co-Worker type might have a completely different set of items: Work days, hobbies, married, etc. Note that the datatypes for each item may change. Some may be Boolean, others dates and still others, simple text. Based on the Type chosen for the person, I want to enter in information for each item, specific to that type of person.

Real challenge: I want to be able to add new Types of people without modifying my data structures and new Items that correspond to each type. Then enter data for item for each person of that type.

So for example:

Andy Andrews is of Type: “Friend”. When I specify that in the UI, I will see the different items that are specific to a “Friend” and it will allow me to enter in data for each of those items for Andy: Hobbies, birthdate, Wedding anniversary, etc.)

Sally Smith is of Type: “Co-worker” When I specify that in the UI, I will see different items that are specific to a “Co-worker” and it will allow me to enter in data for each of those items for Sally: Work days, hobbies, married, etc.

The real trick is that Later on, without modifying the Structure of my database, I want to be able to add a new Category: “Relative”, specify items specific to a “Relative” (Relationship, Age, etc.)

Fred Ferguson is of Type: “Relative” and I should now be able to enter and store information about his Relationship and Age.

I can figure out the UI if I can get the RDBMS structure correct. So, what type of structure would you use to accomplish this goal in a Relational Database? In other words, how would you structure your data tables and relationships to support this desired goal?

Thank you in advance, for your thoughts and comments.

P.S. – All names and examples are fictional. Any similarities between these names and real people is purely coincidental.

Reply | Quote

Replies

Bob,

I’ll take a stab at this but I’m sure others who are better at DBs that I will have other suggestions.

First, You should have a table of Relationship Types, e.g. Friend, Co-Worker, Relative, etc.

You’ll use this table as a lookup in your main database of people to assign the the Relationship type there. By doing this you need merely add a new type {data entry to the table} to this table to have it available in your input form & database.

That said you’ll have to add a new table for each relationship type {sorry but this requires modifying your database structure but I don’t see a way around that} that contains the data fields particular to that relationship type. These tables will be joined on the personId in the main table. You could have all the data fields for each relationship type in the main table but this would not be what is considered good Relational design.

You then build your forms so that when the record is selected in the main table the proper subform is displayed. You could also use a tab control to display the different relationship data.

This is a broad overview but I hope it gives you a start.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you. I appreciate your input. Yes, your approach certainly has merit except for that one little glitch of needing to add tables for each different relationship type. My overall goal here is to not have to adjust the database table structures and relationships each time I add something new. To just add Data to a database.

That being said, as a professional database developer, I have twisted this thing on its head and come up with a variety of approaches, some of which involve creating some tables that contain metadata about the tables and items I want to add, along with their data types, etc. While on the surface it seems as though this might be the answer, it just adds more complexity than I think it is worth. I’d spend more time and money creating a generic type of solution for which neither I, or the client, would be willing to pay.

Ultimately, I (The developer) would still need to be the one to add certain information to the dataase to describe the new Type and the items that go along with it. In addition, I would need to be the one to modify the UI and any queries based on the new types and items. Which of course would require additional time and money.

So, after much thought and agonizing about this, I have come to the conclusion that it is a Cost vs. Benefit equation and in my case, at least with the various avenues that I have explored, the cost far outweighs the benefits for either me or my client. In most cases, the Types can be determined in advance and should the client want to add additional types, that is what we would call an upgrade. I was just hoping that, after all these years, I was missing something fundamental in relational database design. My sense is that I have not missed something but, I would be more than happy if someone out there would prove me wrong!!

Thank you so much for taking the time to make your comments! Much appreciated

Bob

Reply | Quote

Bob,

The only other thing I can come up with is, as you say, convoluted!

You could have a table like:
RelationshipType
Data Element
This would be a combined key to prevent duplicates.

Then you would have a table
PersonID
RelationshipType
Data Element
Data Value

Now you could populate your sub-forms by filling in generic labels Using Data Element names from the first table with text boxes to accept the data and then store both the Data Element Name and Data Value in the second table along with the linking information from the main form.

Now, all you have to do is enter data in table 1 to add a new data element to a particular Relationship Type or add a new Relationship Type.

Of course the initial code to construct the sub-form will be a bear as you have to determine a maximum number of Data Elements allowed for the biggest type {most data elements}, Fill in the form Lables for the Data Elements for the types necessary for that Relationship Type and then hide the unused field labels and entry boxes.

Creating reports is a whole ‘nother problem.

But at least you don’t have to mess with the data table structures.

Is it worth it? Of course upgrade = Revenue stream

I hope I wasn’t too incoherent.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

We do something a bit like that where we create a “characteristics” table that is open ended and data can be added to it until the index runs out – 2 billion more or less, and we apply characteristics to people. RG’s suggestion is along those lines. But the real challenge you are looking at is one that people have puzzled over for a long time. Outlook Contacts is a excellent example of at attempt to deal with those kind of issues, and it depends on who you talk to as to whether it succeeds or not.

Reply | Quote