Noob here. I am trying to create a class or data structure where each entry has one unique name and an unknown number of descriptive tags. I’d like the data structure to be something I could also easily export to some common format (I am guessing that CSV would not be able to do what I am asking since I think I need multiple delimiters somehow?).
For background, I am trying to define this data structure so that later I can implement a way to pull up each entry and see all the tags as well as be able to implement a way to search to see which entries contain a particular tag.
Here’s an example of the raw data I would be looking at which has one unique name, one non-unique number, and an unknown number of non-unique tags.
Name: Jim Age: 47 Descriptors (Tags): Fat, Tall, Wrinkly Name: Bob Age: 88 Descriptors: Sad Name: Charlie Age: 12 Descriptor: Tall, Ugly
Advertisement
Answer
Here’s how I would design the tables.
Person Table
Person ID Name Birthdate
----------------------------------
1 Jim 1973-10-02
2 Bob 1932-06-02
3 Charlie 2008-12-25
Descriptor Table
Descriptor ID Person ID Tag
--------------------------
1 1 Fat
2 1 Tall
3 1 Wrinkly
4 2 Sad
5 3 Tall
6 3 Ugly
The ID fields of both tables are an auto-incrementing integer. They are also called blind keys. They have no meaning other than to connect tables together.
The ID fields of both tables are the primary (clustering) key. The Person ID in the Descriptor Table is a foreign key pointing back to the Person Table.
The name and birth date appear once for each person.
When you want to retrieve the tags from the Descriptor table, you need to specify the Person ID. You can do this indirectly, by using the name or birth date.
SELECT Tag FROM Descriptor Table, Person Table WHERE Person ID Descriptor = Person ID Person AND Name = 'Bob'
The above is not valid SQL, but I hope you get the idea.