Database design best practice

From: Kevin B Ebert <kevin_ebert_at_hotmail.com>
Date: 12 Aug 2002 14:39:30 -0700
Message-ID: <2c5e843.0208121339.3376b72c_at_posting.google.com>

I'm trying to think through the best design approach for the following situation.
I've got an entity called "MainData". In it, there is a primary key, some other fields and a "Service ID". Based on the "Service ID", I'll store different information, i.e., if the Service ID is 1, I'll store location, mileage, etc. If the "Service ID" is 2, I'll store, Spare Tire, Locking lug nuts, and etc. That is, based on a specified "Service ID", I want to store different information relating back to my "MainData" entity. Currently, there are 7 different services and will grow over time. I'm considering two different design approaches...

Design 1:
Create the following table structure:

MainData

---

MainDataID
FKServiceID

Services

---

PKServiceID
ServiceName

Service1

---

FKMainDataID
Location,
Mileage

Service2

---

FKMainDataID
SpareTire,
LockingLugNuts

From this, at run time within my app, I'll know which combination of MainData and Service I need, and I'll have different stored procedures to look up the values accordingly. This seems workable, but my concern is...if I were asked to select all the MainData entities and the relavent service data, I'm SOL. Also, if I need to add new services down the road, I'll need to create new tables and stored procedures to handle the new items.

Design 2:
In the second design, consider a "ServiceData" table that will have a composite key comprised of the Main Data ID, Service ID, and a Field ID where the Field ID will relate to different fields, i.e., location, mileage, Spare Tire or Locking lug nuts. The value field would then store the associated values. The following tables would result:

MainData

---

MainDataID

ServiceData

---

FKMainDataID
ServiceID
FieldID
Value

The problem with this approach, I'll have to iterate through a record set from the ServiceData table to get the values for each Main Data entity, therefore, If I were asked to get all the MainData entities and their corresponding values, there would be a lot of looping going on. Thus, I would be concerned about performance and also the size of the ServiceData table.

So, I can't believe I'm the only person who has come across this problem. Are there any resources available to help me, or does someone have some suggestions.

Thanks for your help in advance.
Kevin Received on Mon Aug 12 2002 - 23:39:30 CEST