How would you represent a hashtable collection in a database schema?

Question

If you were trying to create a domain object in a database schema, and in your code said domain object has a hashtable/list member, like so:

public class SpaceQuadrant : PersistentObject
{

    public SpaceQuadrant()
    {
    }

    public virtual Dictionary<SpaceCoordinate, SpaceObject> Space
    {
        get;
        set;
    }
}

A Dictionary is just a hashtable/list mapping object keys to value keys, I've come up with multiple ways to do this, creating various join tables or loading techniques, but they all kind of suck in terms of getting that O(1) access time that you get in a hashtable.

How would you represent the SpaceQuadrant, SpaceCoordinate, and Space Object in a database schema? A simple schema code description would be nice, ie.

table SpaceQuadrant
{
    ID int not null primary key,
    EntryName varchar(255) not null,
    SpaceQuadrantJoinTableId int not null
                 foreign key references ...anothertable...
}

but any thoughts at all would be nice as well, thanks for reading!

More Information:

Thanks for the great answers, already, I've only skimmed them, and I want to take some time thinking about each before I respond.

If you think there is a better way to define these classes, then by all means show me an example, any language your comfortable with is cool

Answer 1

Relations are not hash tables; they are sets.

I wouldn't organize the database using the coordinates as the key. What if an object changes location? Instead, I would probably treat coordinates as attributes of an object.

Also, I assume there is a fixed number of dimensions, for example, three. If so, then you can store these attributes of an object in fixed columns:

CREATE TABLE SpaceQuadrant (
  quadrant_id INT NOT NULL PRIMARY KEY,
  quadrant_name VARCHAR(20)
  -- other attributes
);

CREATE TABLE SpaceObject (
  object_id INT NOT NULL PRIMARY KEY,
  x NUMERIC(9,2) NOT NULL,
  y NUMERIC(9,2) NOT NULL
  z NUMERIC(9,2) NOT NULL,
  object_name VARCHAR(20) NOT NULL,
  -- other attributes
  quadrant_id INT NOT NULL,
  FOREIGN KEY (quadrant_id) REFERENCES SpaceQuadrant(quadrant_id)
);

In your object-oriented class, it's not clear why your objects are in a dictionary. You mention accessing them in O(1) time, but why do you do that by coordinate?

If you're using that to optimize finding objects that are near a certain point (the player's spaceship, for instance), you could also build into your SQL query that populates this SpaceQuadrant a calculation of every object's distance from that given point, and sort the results by distance.

I don't know enough about your program to know if these suggestions are relevant. But are they at least making you think of different ways of organizing the data?