CODEWITHSUNDEEP
mysqldatabase-design
Tobias Fünke
Tobias Fünke

Reputation: 2024

Database layout for category-specific fields

I am working on a service request system for a city government call center. Citizens call in, and the call center agents input their request into a web form, which is then transmitted to the appropriate city agency. My question here pertains to the web form.

The best way to explain this is to give an example. If Caller A is calling to report graffiti, after the agent selects 'graffiti' from the category list, I would like there to be separate fields for things like:

Whereas if Caller B is calling to report an abandoned vehicle, the fields offered should be things like:

Obviously fields like location/address are pretty universal to any category of call, but the other fields are not, and are often relevant to only one type of call. So the question is how do I layout my database to accommodate for this?

One idea that comes to mind, of course, is just to have all of these fields in the calls table and somehow specify which fields pertain to which types of calls, leaving the rest null (license_plate would be null for a graffiti complaint, etc.). But the number of fields could certainly add up with that option.

Another idea I had was to have separate tables, i.e. a graffiti_calls table, and have the fields defined there, although certain categories of calls would not need to have custom fields (such as someone calling to get a phone number).

Lastly, a more complex way I thought to achieve this would be to have a category_questions table with id, category_id, question and a field_responses table with id, call_id, question_id, response. This option seems to be the most versatile but also the most complicated.

What would be the best way to do this? Note that I have experience with PHP/MySQL; this is more of a concept question. Thanks in advance!

EDIT: So I played around a bit with the 3rd option, and came up with this layout. I'm still open to suggestions though if you think this could be achieved in a better way.

Upvotes: 0

Views: 196

Answers (4)

Jesse Cohen
Jesse Cohen

Reputation: 4040

Given that you want to search on the variable fields, an acceptable structure would be to have a master table with all of the global fields, e.g. id, address, date, etc...

And then a table of attributes with the following format:

`id`, `field_name`, `field_value`

So for the graffiti example, in your main table you'd have id=1, problem_type=graffiti, address='main street ...'

and then in the attributes table you'd have several rows:

id=1, field_name=paintcolor, field_value=blue
id=1, field_name=ladder, field_value=FALSE
etc...

This would allow you to search on variable fields.

To store the questions, I'd have a table that mapped problem_type, field_name, question_text. An example entry would be:

graffiti, ladder, 'Is a ladder required?'
graffiti, paintcolor, 'What paint color is required?'
etc...

You can extend this as you wish, and get fancy adding extra fields like input type etc to this table so that you can intelligently generate html forms, e.g. for the ladder one you might only want a selectbox with yes/no, you could add column(s) to the questions table specifying the input type, the eligible values, a validating regexp etc... It all depends how elaborate you want to get.

Upvotes: 1

Thomas
Thomas

Reputation: 64645

Given that there are data integrity requirements for each type of request, I would setup a subtyped architecture:

Create Table Request
    (
    Id ... not null Primary Key
    , RequestDate datetime not null
    , ...
    )

Columns in the Request table would apply universally to all types of request. Then for each type of request, you create a separate table in a One-to-One relationship. You want to try to group requests with similar attributes together if possible.

Create Table DefacementRequest
    (
    RequestId ... not null Primary Key 
    , Location ... not null
    , Description ... not null
    , RequiredEquipment ...
    , ...
    , Constraint FK_DefacementRequest_Request
        Foreign Key ( RequestId )
        References Requests( Id )
    )

Create Table MotorVehicleRequest
    (
    RequestId ... not null Primary Key 
    , Make ... not null
    , Model ... not null
    , VIN ...
    , Color ...
    , Location ...
    , ...
    , Constraint FK_DefacementRequest_Request
        Foreign Key ( RequestId )
        References Requests( Id )
    )

That each type of request has its own set of required data would be reason enough to use separate tables where you can enforce those types of rules (e.g. Vehicle requests must have a VIN or Make and Model). If you try to use an EAV, you will have no ability to enforce those rules at the database and it will make reports based on specific types of requests incredibly cumbersome.

Upvotes: 0

AndreKR
AndreKR

Reputation: 33678

You already came up with all three possible models. Unfortunately there is no definitive answer to your question.

The latter method (which is called EAV) is often used with stuctures that are frequently changed.

By the way, I think it's ok to use just one table ("answers") with a string key for the question.

Upvotes: 0

rayman86
rayman86

Reputation: 1395

You could have a second table with the subclasses, with the entries being a description and the name of the master class it belongs to. If you wanted to get even fancier you could have 3 tables total.

Table one being your 'master' trouble entry, and it would contain foreign key ids for the master category and the subcategory.

Table 2 would be your master category table, with the PK and the description of the category.

Table 3 would be the subcategory table, with the PK and a foreign key to the master category table, as well as the subcategory description.

Upvotes: 0

Related Questions