How do I design a DB Table when my sources are VENN DIAGRAMS?

Imagine 3 circles. Each circle has some numbers

Circle 1 has the following numbers

1, 4, 7, 9

Circle 2 has the following numbers

2, 5, 8, 9

Circle 3 has the following numbers

3, 6, 7, 8, 9

Circle 1 and Circle 2 share the following numbers

10, 9

Circle 1 and Circle 3 share the following numbers

7, 9

Circle 2 and Circle 3 share the following numbers

8, 9

All three circles share

9

Each number represents symptoms so in my case

Circle 1's numbers could be symptoms for a short circuit Circle 2 could be numbers for a component failure Circle 3 could be numbers for external issues

each of the three issues share certain symptoms

If given #9, we wouldn't be able to deduce the problem but could display a list of all issues involving #9

If given more #'s, we can attempt to show relevant issues.

My problem is how do I put this into a table so my code can look things up.

My Database of choice is SQLite3

@Vincent,开发者_StackOverflow中文版 the only issue I have is that there are several variables. I have variables called t1, t2, t3, a1, a2, a3. Each of these variables are symptoms. The user interface for my application allows the user to input a value for each variable then I want to check the DB. All values for each symptom can be any value in the 3 circles (mentioned in original problem)

---

Create 3 tables as:

symptom = (symptom_id, descr)
problem = (problem_id, descr)
problem_symptom = (problem_id, symptom_id)

e.g.

Symptom
Symptom_id    Desc
1             doda
2             dado
3             dada

Problem
Problem_id    Descr
1             Short Circuit
2             Component Failure

Symptom_Problem
Symptom_id    Problem_id
1             1            --- doda is a symptom of Short circuit
2             1            --- dado is a symptom of short circuit

etc.

You can then query and join to determine the problems based on the symptoms.

---

I would suggest 3 tables: symptom, problem, and problem_symptoms. The latter would be a join table between the first two.