How would you set up a database for this type of problem?

Question

I'm quite a beginner at database design. I have two problems that I'd like to solve using a database, but am not sure how to deal with an unknown number of fields.

Here are the two types of data I'd like to store (they represent the same schema issue I think):

I want to store a bunch of coordinates for a map route. The proposed DB would look like this:

Name            Point 1    Point 2  ...
----            -------    -------

Jogging Loop    5, 10      6, 11    ...

And the other problem I'd like to solve is a partial solver for anagrams. I want to go through a dictionary and alphabetize the letters in each word. For each alphabetized string, I'd like to associate the words that can be formed from that string. IE

Alphabetized String        Word 1     Word 2     Word 3
-------------------        ------     ------     ------
abet                       abet       beat       bate

The part I don't know how to solve is the unknown number of columns. There must be a better way than to have an arbitrary # of columns based on the max length of any row in the table. Do I need two tables and do I join them somehow?

Thanks for shedding light on how this is typically addressed.

Answer 1

You don't have an unknown number of "columns".

You have a two-part key.

Map Route:

Name, Point, Sequence
"Jogging Loop", (5, 10), 1
"Jogging Loop", (6, 11), 2

Alphabetized String

Name, Word, Sequence
"abet", "abet", 1
"abet", "beat", 2
"abet", "bate", 3

Answer 2

make two tables related by Routes.RouteID = Points.RouteID

Routes
------
RouteID RouteName
1       Jogging Route 1
2       Jogging Route 2

second table:

Points
------
Point RouteID
1,2   1
2,3   1
3,4   2

Answer 3

You need to not try to represent it in a horizontal table you want to represent the data vertically. So instead

Name     Point    x    y
----     -----    -    -
Route    1        5    10
Route    2        6    11
...

Same pattern for the other one. Basically just change your dimension so you are adding rows instead of columns.

Answer 4

The usual solution for a problem like this is to normalize the tables.

That will result in 2 tables in this case.

1. The table with the original route or word for the second example. As columns this should have:
   • A primary key (an autoincrementing field named id would be my recommendation)
   • The name or word column.
2. A table with the coordinates / anagrams.
   • Also a primary key (a primary key is essential in any table)
   • A foreign key to the first table
   • A column with the coordinates / anagram