Generalization/Specialization design

[p27br]

Hi,

I am stuck in my db design : I am designing an application for managing our deliverables production.
a client can receive several types of deliverables but these deliverables types can have different attributes, some of these are mandatory attributes for one type but not for another. So all in the same table is a problem.
I was thinking of subclassing the deliverables types into different tables maybe.
how should i best do this ?

thanks

 

[JamesLean]

One way would be to have your main Deliverable table, including any fields that are common to all types of deliverable.

Then have a DeliverableAttribute table something like this:

DeliverableAttribute
AttributeID (PK)
DeliverableID (FK to Deliverable)
Name
Value

Might not be the best method, depending on your specific requirements, but it's one option!

--James

 

[p27br]

i want to be able to pull out all deliverables for one client regardless of their type
i want to be able to input a new deliverable on a single form. Maybe a combobox 'Deliverable type' which selects which attributes will be editable on the form.

 

[JamesLean]

Depending on the number of attributes that will differ between deliverable types it may be easier just to have all the possible attributes as columns in one table and only fill in the ones that relate to the current row. I know this goes against correct normalisation rules but it would probably make querying/updating the tables much easier.

--James

 

[p27br]

yes that's what i have done for the moment , but i was thinking maybe there was a better way

thanks anyway