Polymorphism and table structures

   At work we are looking to remodel some of of tables used by our software (currently using Foxpro free tables, i.e., DBFs/CDXs/FPTs). We will be sticking with Foxpro for the time being but are planning to eventually migrate all of the free tables to a slightly more modern dbms (maybe Foxpro database, MySQL, etc). One of the tables in question is defined as follows (true table names have been changed to protect the innocent)... ;)

MAP.DBF

MapName     C/20/0
NbrObjects  N/2/0
PtrToObj    M/6/0

(associated with this is a memo file called MAP.FPT)

For arguement's sake let's just say that the MAP.DBF table can contain up to 99 objects in a map definition and there are 60 type of objects that can be associated to a map. For example:

Obj ID    Obj Type         Obj Structure
------    --------         -------------
1         Line             <line type - C/5/0><line size - N/3/0> 
2         Label            <label name - C/20/0>
3         Arc              <start - N/5/0><end - N/5/0><midpoint -
n/5/0>
4         Symbol           <todisplay - L/1/0><assoc. bmp - C/20/0>

As can be seen each object has a different structure (thus the polymorphic table). To manage this today, we have a routine that will process a map's components based on a component's id (think of it as a big IF or CASE statement with all of the validation for each object type managed there). This works fine but we have been asking ourselves if there is a better way to manage the table.

If we move to a "modern" dbms where that engine can do a lot of the validation then the above polymorphic table won't work (because each field has a different structure, the dbms engine won't know how to validate its fields - unless it can somehow use the object ids to know which fields to validate).

One possibility is to create 60 tables, where each will house a specific object type. A join table would keep track of which objects belong to which map. A modern dbms engine will be able to handle this but we're talking about data being spread across 60 tables rather than keeping it in one. I'm just not crazy about this idea...

Anyway, what I'm wondering is what is the preferred approach? Is there an approach that we haven't considered (there are a few others we've thought of but this post would get too long).

Do people just generally shun polymorphic tables?

I'd appreciate getting other people's opinions/approaches.

Regards,

Rollback5 Received on Wed Oct 23 2002 - 01:18:22 CEST