DB design suggestions needed

[GabeC]

I need advice on how to design a database -

I currently have three objects:
Service
Agreement
Contract

Any of the objects can be made up of any of the other objects.

Ex.
Service A is made up of:
- Agreement 1
- Contract I

Agreement 1 is made up of:
- Service B
- Agreement 2
- Contract II

Service B is made up of:
- Contract III
- Contract IV

The types of objects can change.
I want to add the new object Warranty

Now:
I have for objects:
Service
Agreement
Contract
Warranty

Service A is made up of:
- Agreement 1
- Contract I
- Warranty a

...


What are some goods DB designs for this type of data?

They type of queries I will be wanting to perform would be:
If I make a change to Warranty a, list all the objects that will be affected.


Thanks for your input!

Thanks,

Gabe

 

[PerBylund]

It seems to me you should keep the solution open for more objects and any combinations and thus explicit relations might be quite annoying when developing the system. I would make a "centralized" connections table for all other tables, e.g.

- Service Table
- Agreement Table
- Contract Table
- Warranty Table

and then a connections table with implicit connections:

Connections (ObjectType, ObjectId, ConnectedToType, ConnectedToId)

Like this:

Service record with ID = 100 is connected to Warranty with ID = 200 and Contract with ID = 300. Connections table gets these records:

SERVICE, 100, WARRANTY, 200
SERVICE, 100, CONTRACT, 300

and so on. You can easily add relations in any way and as many as you like. But you need to make sure you explicitly code all connections so that you don't "forget" them when deleting records...

 

[PerBylund]

I guess I need to clarify the solution a bit.

What I'm suggesting is a table without explicit relations with other tables, but with implicit relations based on keywords (in the example the keywords are "service", "warranty" etc). The combination of keyword and the ID from the specific table (e.g. Service table for "service" keyword) tells you what record it is.

If you wish you might want to make all keywords numbers instead (int), which probably would make the database faster (but much harder to understand). You can even add a table with meta information so that you know what it means (i.e. a table simply listing that "1" is "service" and perhaps what table holds info on services).

 

[GabeC]

Thanks for this suggestion.

Following this design, when an application administrator uses the application to create a new object type (using the application Admin web page interface), the application would also need to create a new table in the database.?

Do you see any issues adding and removing tables in this fashion? (The DBAs will not be making this type of change as the application administrators will need the ability to make them.)

Thanks again.

Thanks,

Gabe