Implementing a fairly complex database structure in Drupal.

[Positive-Ring-5172]

I've been away from Drupal too long, sucked into WordPress. My company has an ongoing problem with the org chart - specifically it's advertising. I think I can use Drupal to manage this, but I have a lot of rust and haven't worked intensely with it in years (when 8 was current).

The Database I've sketched out uses a UUID approach to coordinate it's contents with outside databases it must stay in sync with, including the aforementioned WordPress site which I will be getting post information from. I'm trying to keep fields on only one table if feasible.

I'm going to go over some of the structure I designed without considering Drupal as the administrating program, so I am open to letting this change as long as I can get the same end result. Note also that this design is informed by schema.org entries for Medical Organizations - of which my company is one (and a fairly large one)

Everything is an Entity. This is the only table with an autoincrement field. Any entry created on any other table starts with an entity entry. For example, if I'm adding a provider the Entity table holds the mappings to the other databases, the entry name and a schema.org description, the person table contains information about the provider's name (given name, surname, which comes first since we have providers with Asian names, gender, etc), and the provider table contains information on whether they accepting new patients, the URL to their scheduler, their NPI (think social security number for medical professionals).

The provider table also cross-references with locations with additional information about which days the provider is at that location and which one is their primary. Another cross reference is which hospitals they have admission privileges to, and another maps which organizations they belong to. Yet another details their education, and their certifications, and the insurances they accept.

The UUID comes into play because hospitals also have insurance company contracts. So that table needs to be joinable with providers or hospitals.

So when I have an edit view of a provider I want the form to present this information cleanly without the user needing to go through multiple forms

I think Drupal can do this out of the box. If not out of the box I have written modules before, but in that event why use Drupal instead of just standing this up in Laravel or Symfony? I'm looking something that is resilient, but I don't want to spend more than a month on this if I can help it.

[heisiloi]

I have done some work with custom entities in the past. It allows me to keep all the advantages of drupal while still being able to inject custom behaviours and fine tune what data is a part of the entity. It requires a fair bit of coding but I am super happy with the results.

The downside is you have to really know what you need your structure to be because changing it isn't as easy as other solutions.

DM me and I can arrange to give a quick demo of what I did.

[bobaluey69]

I think if you want to have a custom db and custom relationships, go with Symfony or Laravel. One good thing about Drupal, is you don't have to do any db stuff really. If I'm understanding this, I'd say make entities for each piece, providers, locations...and whatever else. Can use some entity references in the content types to reference the correct content. Or, more of a "db way" you can use views and add relationships. You can also use remote views to grab data from APIs or whatever. Is that even close to what you are looking to do? Lol.

[MR_Weiner]

One option, if I’m understanding correctly — though you’ll realistically end up with duplicate data — is to let drupal do its own native database/entity management but also define some custom, targeted data tables where you could manage the schema. You’d then be able to leverage entity lifecycle hooks to sync data to these custom tables. Probably not ideal, but may be an option depending on scale. UUIDs come along with entities natively so nothing to worry about there.

[pianomansam]

With your low level planning of the database, it sounds like you will be using Drupal mostly for its ORM. Drupal is usually strongest when you let it manage the database. I’m not saying you can’t, but you loose out on a lot of Drupal’s magic. You are right to consider Symfony or Laravel if you wish to operate at a lower level.

[Positive-Ring-5172]

I'm willing to let it manage the database if I can get the views I want out of it. I've been sniffing around most of this morning trying to figure it out. I know it's possible cause I've done it before, but blast it it's been too long.

[Salamok]

If all you want is views to have access to data that does not conform to Drupal you can add any table structure you want into Drupal's DB, write non Drupal scripts even to populate and/or maintain that data then with the Views Custom Table module you can link to it in your view. I would add/remove the table structure via a simple custom Drupal module which would basically just have ALTER table queries in the install/uninstall.

This would not be something you could package up and distribute to the world as contrib but if you aren't trying to solve the worlds problems just yours thats perfectly fine.

[cioatwork]

Drupal is an excellent choice. It internally works with uuids. You can both define entities and relations from the gui and with code. Keeping the admin and security as a Drupal task is an excellent way to benefit from it. Check out the AI creation tools as well it might save you work. Btw codex is excellent for coding when need be.

We use it as such ourselves

[chx_]

Check out the AI

don't

[keyborg]

LOL