MYSQL: How can I store/retrieve a value based on the 3+ other values in the same table?

Question

Let's say I'm making a program for an English class. I'd like to store data in this way:

ID      Object
0       Present Tense
1       1st person singular
2       To Be
3       I am

How can I retrieve the value for ID 3 based on IDs 0-2? The only thing I can think of is:

ID      Object     FromIDs
3       I am       0,1,2

The problem with this is that I'd have to do a fulltext index and I think this table is going to get pretty large. I don't want separate tables for different types of objects, if possible, because I don't know what I'll end up doing with these objects and I want as much flexibility as possible. I could have a second table relating IDs to each other, but I've only done that successfully relating a column from one table to a column to another.

Thanks in advance!

Answer 1

You need to break the data into different tables. Have a table that stores the "tense" and another that stores the type "1st person singular".

Answer 2

Can you explain your problem a little more. From what you have I'm not sure if you're trying to go down the path of Entity-Attribute-Value or probably what is more likely is that relational database is not a good fit for your problem; you may need to use some sort of tree data structure. If you update, I can try to provide a better answer.

Answer 3

What I've decided to do is a combination of what was suggested and what I originally thought. I'm going to have a master list with IDs that are auto-incremented and copied to other tables. That way, I have properties of different parts of speech separated, but still have everything relating to everything else.

Answer 4

This is really not a good fit for a relational database. Sorry, you're trying to drive a nail using a screwdriver.

When you have no distinction between an attribute type and a value, you're modeling semantic data. The open standard for this type of data modeling is RDF.