Should I rethink my database project if it seems to me that I need to store the column names of one table in another?

Question

I am very new to database design, and I have a rather high order from a client with very complex relationships in their data. What I'm trying to do is write the logic for recommending products based on the conditions that the client has. However, there are some interesting filters that need to be applied depending on the values ​​of various other data bits. For example, some products are only for {men | women}, so depending on the value of say customers.customer_gender product is not recommended.

Of course, I could do all this in PHP, but it would be better if I could represent it in a database and just make it a form interface. That way, they can customize and modify these relationships as they add new products without releasing me to customize the code! My problem then is inexperienced. I do not understand how I should present this information in tables.

EDIT 1 - Try an example

Imagine you have the following tables:

Client

 id name gender -- ---- ------ 1 Bob male 2 Mary female 3 Steve male 

condition

 id name -- ---- 1 Headaches 2 Allergies 3 Low Energy 

Products

 id name description -- ---- ----------- 1 Product A anti-allergy 2 Product B energy booster 3 Product C pain reliever 

and some other tables using foreign keys to determine the relationship between them, for example, which products are recommended for which conditions (a two-column table that combines the product identifier and the condition identifier), and which customers have what conditions.

Now I have certain products that can only be recommended to men, for example. Or only to people who are not sensitive to caffeine. Or who has a certain condition with a certain severity (where severity is an additional column in the table linking clients and conditions containing an int value from 1-3). How can I present these kinds of relationships ("recommend product X for condition Y, other than expression Z") in clean database tables?

Answer 1

You can solve this problem as a model:

There are two key elements that can help you solve a design problem.

• There are tables in which the rules for using the product are indicated, as well as when the product is not applied.

• Include in the CONDITION table any situation that may be involved either in the pro rule or in con.

For example: Viagra is contraindicated in patients with the condition "has a uterus."

CONDITION_SEVERITY implies that the patient has a condition, and also allows you to record the score on any kind of scale that you need to use to say how bad it is. You can use a similar severity score in the INDICATIONS and CONTRAINDICATIONS tables to say that a product is only applied when the condition is at least as bad or should not be used if the condition is bad or worse.

Answer 2

The answer to your headline is a definite yes , it will never be needed. If you want to create an extensible list of conditions, you can define this in the static structure of the table. Consider something like this.

• Customer table: id, name, etc.

• Product table: id, product_name, etc.

• Attribute table: id, age, gender, etc.

• Attribute value table: id, attribute_id (foreign key reference to attributes), value

• Customer attribute table: id, customer_id (FK for customers), attribute_id (FK for attributes)

• Recommended table: id, attribute_id (FK for attributes), attribute_value_id (FK values ​​for attributes), product_id (FK for products)

Here # 6 will be the core of your recommendation engine, linking these attributes with these products.

This is just an example, but I want to say that you can create a clean static database structure that is fully extensible; no need to resort to madness like dynamic table and column names.

Answer 3

Consider this construction:

Tables

• Users (UserID PK)
• Medication (MedicationID PK)
• Symptoms (SymptomID PK)
• Severity (SeverityID PK)
• Treatment (TreatmentID PK)

Data examples

Users

 UserID Username 1 john.smith 2 jane.doe 3 bob.jones 

Medicines

 MedicationID Name 1 Tylenol 2 Excedrin 3 Morphine 4 Midol 5 Tums 6 Pepto 7 Anzemet 

Symptoms

 SymptomID Name 1 Headache 2 Cramps 3 Vomiting 4 Dizziness 

Seriousness

 SeverityID Name 1 Feels bad 2 Feels really bad 3 I want to die 

Treatment

 TreatmentID SymptomID SeverityID MedicationID 1 1 1 1 2 1 2 2 3 1 3 3 4 2 1 1 5 2 2 4 6 2 3 2 7 3 1 5 8 3 2 6 9 3 3 7 

Using the data above a User , you can come and say that I have a headache ( SymptomID of 1 ) with a severity level of 2 . This would suggest Excedrin medicine.

In the "Treatments" table, a remedy based on Symptom and Severity can be offered.

 SELECT m.Name AS MedicationName ,sy.Name AS SymptomName ,sev.Description AS SeverityDescription FROM Treatments AS t INNER JOIN Medication AS m ON t.MedicationID = m.MedicationID INNER JOIN Symptoms AS sy ON t.SymptomID = sy.SymptomID INNER JOIN Severity AS sev ON t.SeverityID AS sev.SeverityID