Database design for records with different sets of fields

Question

Question

1) I am looking for a suitable way to develop a web application and, in particular, a database schema, to have a base table with all the main fields for this service, and then, depending on the type of service, I will need an additional set of fields to communicate with the service.

I need to do this in such a way that the search is straightforward and offers reasonable performance. I'm probably looking at some type of full-text search, but you will only have 5 concurrent users of the application.

My ultimate goal for the application is to be able to pretty much search the entire database for any given keyword and return all related records. Initially, I wanted to split the specified fields for each type of service into separate tables with their own columns, but I think this could lead to more complex search queries (many JOINs) or several more search queries.

For any proposed solution, can you indicate why you think this would be a good fit?

2) My other problem (we hope will become clear below) is that my project currently consists of a table of “types of services”, where I would define the main types of each product, where each service is then an “instance” of this product.

My problem here is that I feel that I will probably be able to duplicate most if I have both products and a table of service types. Therefore, to avoid this duplication, this is the main thing that I try to achieve in my design.

More details

I am currently writing a custom web application that is used to track the services provided for each client, not only for billing (billing cycle, start / end dates, prices), but also for documenting these services (associated user accounts, IP addresses, physical assets, etc.).

Each service is based on a “product” table that defines the name of the base product, price, billing time, description, etc. We could have several products of the same type (for example, for different plans a certain type of product). For example, we have the following products:

• WebHosting One Overall Plan
• WebHosting Two General Plan
• WebHosting Three Overall Plan
• Dedicated Server Plan
• Virtual Dedicated Server Plan
• Virtual Dedicated Server Plan 2

Currently, the problem is that we have a number of fields that are common to any given service, but we also have several fields that change depending on the type of service being monitored. Depending on the type of service, I will show the basic form for all services, but also the corresponding form for adding / editing, etc.

For example, we have the following types of services, and each product (as shown above) refers to one of these main types of services:

• Shared web hosting
• Dedicated Hosting
• Virtual Dedicated Hosting
• ADSL
• ...

My possible solution

The current solution is multiple tables

Currently, my database has:

ServiceTypes

• ServiceTypeID INT PK
• Type VARCHAR (40)

product

• ProductID INT PK
• Title VARCHAR (40)
• Description TEXT
• DECIMAL Price
• BillingDuration INT
• TypeID INT (FK ServiceTypes.ServiceTypeID)

Service

• ServiceID INT PK
• ProductID INT (FK: Product.ProductID)
• Title VARCHAR (40)
• Description TEXT
• DECIMAL Price
• BillingDuration INT
• Active bit
• StartDate DATETIME
• EndDate DATETIME

These are the main tables for any service, then I have additional tables for advanced properties:

ServiceADSLInfo

• ServiceADSLInfoID INT PK
• ServiceID INT (FK: Service.ServiceID)
• FNN VARCHAR (10)
• VARCHAR Username (20)
• Password VARCHAR (20)
• LocationID INT (FK: Locations.LocationID)
• ModemAssetID INT (FK: Assets.AssetID)

ServiceVirtualServerInfo

• ServiceVirtualServerInfo INT PK
• ServiceID INT (FK: Service.ServiceID)
• ServerName VARCHAR (20)
• IPAddress INT (FK: IPAddresses.AddressID)
• HostServer INT (FK: Assets.AssetID)
• VARCHAR Username (20)
• Password VARCHAR (20)

ServiceSharedHostingInfo

• ServiceSharedHostingInfoID INT PK
• ServiceID INT (FK: Service.ServiceID)
• VARCHAR Host Name (50)
• HostServer INT (FK: Assets.AssetID)
• DiskSpaceQuota INT
• BandwidthQuota INT

Another solution is a single table

I am going to store all the service-related information in one table, regardless of the type of service, and just have NULL values ​​if they are not needed for this particular service.

• ServiceID INT PK
• ProductID INT (FK: Product.ProductID)
• Title VARCHAR (40)
• Description TEXT
• DECIMAL Price
• BillingDuration INT
• Active bit
• StartDate DATETIME
• EndDate DATETIME
• VARCHAR Username (20)
• Password VARCHAR (20)
• FNN VARCHAR (10)
• LocationID INT (FK: Locations.LocationID)
• AssetID INT (FK: Assets.AssetID)
• ...

I feel this may be an easier solution to work with in terms of search, since to serve any data related to the service, I can just use a full text search in one table and not worry about joining records together.

My main problem here was that in the end I came across a table with 30+ columns, which seems to get pretty dirty. Another thing is that this does not solve both of my problems, because I still need to have a table of basic services to find out which fields I need to use for any given search, and therefore still have some coincidence with my product table.

I am wondering if it is impossible to avoid some coincidence with the product table?

Attribute Value Object Model

I also reviewed this project. All in all, I feel that this is too much for me, because I don't need things that are so flexible and dynamic. We will need a set of field groups depending on the type of service, but I cannot change the data that we need to collect for each type of main service, so this can be static.

It also seems to me that the application logic needed to implement this level of flexibility will be too complex for the benefits it brings.

You need to determine the type of HTML form field to display depending on the type of field requested from the database, etc. It just sounds painful.

Please let me know if there are any details that I can provide! I hope everything is clear.

Thanks!

Answer 1

I think it all depends on how you plan to move forward, especially with the new services included in the system, all of them are valid approaches, but they all have pros and cons.

with the first approach, you get a clean main table, but then for each service you need to create a separate table, for new services you have to continue to do this, and for your application this can add some complexity, since each service will need its own set of queries to pull data (not sure if your architecture is here, so it’s a blow in the dark. I personally think it will hurt in the end.

an approach with a denormalized table will be easier to query, but then you can create a monster table with a lot of optional data for certain types. a slightly different approach might be to add common fields, i.e. 10 fields called numX (where x is from 1 to 10) that contain numbers, 10 called textx, etc., I think salesforce uses this aporoach for custom fields for customers.

the approach with the key field value, as you say, is the most flexible, but you lose things like type recognition, everything should be the same type in the database at least.

it depends on the nature of your application, performance for 5 concurrent users should not be a problem, so perhaps ease of implementation should be. general approach schemes (a sales approach) may work here and may cover you for other services that move forward and in the future, not much for you, but it depends on how many changes you plan.

if the changes are consistent, it will be loaded if services with different fields, etc. The key / value approach is likely to be your best bet, but at this point you can also look at some nosql approaches, as they might fit the bill here, but mysql worked anyway, just opening a discussion.

Update

to go along with the comment, if you do not change the changes in the services too often, I would skip noSQL, as this will add complexity to your development, which probably will not help you.

As stated, the types of services are likely to change too much, then I think a denormalized general approach might work for you. Thus, an application can have one area for services, and you can treat additional properties as “custom fields” and add as needed. that way you use the app. One unfortunate side effect is that you have to manage this in your application somehow through some kind of logic to check if it is there or not, and you have to pull all the fields, regardless of whether they are filled or not, for your needs present, there may not be a huge compromise.

An example (very simple) example of a general approach.

• Common fields, which are your primary fields that are distributed among all services.

This can make the search a little painful, because you can (depending on the mechanism) include all fields in the search