mercredi 19 septembre 2018

Is it possible and a good way to construct database in a dynamic way?

My question is about the specific scenario and what approach I should take in order to make loose coupling and open/closed principle the best way. I'll explain my scenario in a deep detail so that you understand what I'm facing in a deep way. Sorry, It's gonna be a long text . I'm really sorry in advance. Thank you guys anyway.

So I'm creating a website. On that website, one type of users can ask for any kind of service that's related to transports. (he might ask that he needs his cargo to move from one place to another, he might need a great car service for his guests, he might need a construction transport, he might need a transport for excursion, so anything that's related to transports). On the other side, there're one type of users that have all these kind of services(they have trucks and drivers who can go, pick up the client's cargo and move it to somewhere, they have a construction trucks, they have a transport for excursion and so on). there'll be different types of users(admin, client,service_provider, issuarencers, bankers, drivers). I've already mentioned two types of users (clients and service_providers).

So to start creating such a complex website, That's what I've decided. An admin is the one who adds transport types. Then the admin also adds services(the service might have the children-subtypes). Let's say the admin wants to add a new service - cargo movement. Then he also wants to add a new service - Business class trucks. Consider that those services need to have different kind of columns. So we have two services. Now, imagine what I could do. I could construct mysql tables the way that only two tables are needed even though on the website there might be 15 different kind of services.

enter image description here

Like this. This is a dynamic way. so For each service I won't have to come as a developer and add new tables for each one. What could happen is when an admin adds a new service, he specifies the name and it goes to the first table specified on the picture. Then if he wants this service to be the leaf, he goes and adds columns for this service.(those columns that will be needed when a client goes to that service page and needs to request specific business class or cargo movement). Imagine if a client goes to cargo-movement service, he has to fill the form(cargo weight, cargo initial_address and so on.) so these properties or columns should be added by admin from back-end. This could be a possible and it really is. But now imagine when an user(client type) goes to the website and chooses the cargo-movement service, what I do is with that id, I make the query and get the results from service_types and service_type_columns and view gets generated. But as I look at my future for this project, it's possible that it might get more complex and I don't know this approach(generating dynamic mysql and dynamic views in front-end would suffice at all).

I think I explained the situation, maybe not as clearly as I intended to, but you get the idea.

My question is: should I go for this dynamic approach(dynamic mysql - so that for services i don't go to mysql and create new table and different columns for each new service and also dynamically generated views in front-end) or should I create a new view for each service, and add new table for each service? What I wanted to do was to let the admin create a new service from back-end and that new service would start working for all user types without my interference at all. What do you think?

Aucun commentaire:

Enregistrer un commentaire