Data Modeling of Entity with Attributes

Posted by StackOverflowNewbie on Stack Overflow See other posts from Stack Overflow or by StackOverflowNewbie
Published on 2010-06-02T22:49:06Z Indexed on 2010/06/02 22:54 UTC
Read the original article Hit count: 317

Filed under:
|
|
|

I'm storing some very basic information "data sources" coming into my application. These data sources can be in the form of a document (e.g. PDF, etc.), audio (e.g. MP3, etc.) or video (e.g. AVI, etc.). Say, for example, I am only interested in the filename of the data source. Thus, I have the following table:

DataSource
Id (PK)
Filename

For each data source, I also need to store some of its attributes. Example for a PDF would be "numbe of pages." Example for audio would be "bit rate." Example for video would be "duration." Each DataSource will have different requirements for the attributes that need to be stored. So, I have modeled "data source attribute" this way:

DataSourceAttribute
Id (PK)
DataSourceId (FK)
Name
Value

Thus, I would have records like these:

DataSource->Id = 1
DataSource->Filename = 'mydoc.pdf'

DataSource->Id = 2
DataSource->Filename = 'mysong.mp3'

DataSource->Id = 3
DataSource->Filename = 'myvideo.avi'

DataSourceAttribute->Id = 1
DataSourceAttribute->DataSourceId = 1
DataSourceAttribute->Name = 'TotalPages'
DataSourceAttribute->Value = '10'

DataSourceAttribute->Id = 2
DataSourceAttribute->DataSourceId = 2
DataSourceAttribute->Name = 'BitRate'
DataSourceAttribute->Value '16'

DataSourceAttribute->Id = 3
DataSourceAttribute->DataSourceId = 3
DataSourceAttribute->Name = 'Duration'
DataSourceAttribute->Value = '1:32'

My problem is that this doesn't seem to scale. For example, say I need to query for all the PDF documents along with thier total number of pages:

Filename, TotalPages
'mydoc.pdf',  '10'
'myotherdoc.pdf', '23'
...

The JOINs needed to produce the above result is just too costly. How should I address this problem?

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql