0

Currently I am trying to create a database design that is long lasting. But I hit a point where I not know how to continue.

I have a object table in my database. That tables stores one unique object (With a incremental ID). However for the application I am creating attributes about that object are required (extra information so a sort of metadata for that object).

These are now five different attributes per object. But I already know in the upcoming future they want more attributes added to a object so they can get more information about it.

If it would have stayed with those five attributes a would create extra columns in the object table.

But now I am thinking to create seperate attributes table where I store that data. And I was thinking in the sorts of:

Table: attributes

Columns: object_id, object_type, object_data. Is this the best practice or what are your thoughts on the matter?

1
  • It seems you are looking to store unknown structures, so a relational database where you need to know which data you will be storing beforehand might not be the best solution. XML or No-SQL solutions will enable you to store data with different compositions in the same place, and delay the problem of how to query them to a later point. If you already know which objects you will be holding, then create a different table for each one, with their own columns. Don't run into the anti-pattern of the God Table. – EzLo Mar 21 '19 at 12:19
1

You could create tables for Attributes and its values. A simplified schema below:

CREATE TABLE [dbo].[AttributesList](
    [Id]            INT IDENTITY(1,1) NOT NULL,
    [ObjectType]    VARCHAR(32) NOT NULL,  -- wich objects have the attribute
    [AttributeCode] VARCHAR(32) NOT NULL,  -- short attr identifier
    [Kind]          INT NOT NULL,          -- kind of the attribute's value
    [Name]          VARCHAR(80) NOT NULL
)

CREATE TABLE [dbo].[AttributesValues](
    [Id]            INT IDENTITY(1,1) NOT NULL,
    [ObjectId]      INT NOT NULL,  -- object.object_id
    [Kind]          INT NOT NULL,          -- AttributesList.Id
    [Code]          VARCHAR(32)  NULL,  -- for short strings
    [Flag]          INT          NULL,  -- for int values
    [Info]          VARCHAR(250) NULL,  -- for long strings
    [Amount]        MONEY        NOT NULL  -- for money
)

You'll be able to add as many attributes for some ObjectType as you need, and store their values in AttributesValues table in a column that depends on [AttributesList].Kind value.

1
  • This is the Entity-Attribute-Value model and works really well for this type of scenario. My only comment here would be that the MONEY datatype is being deprecated, use DECIMAL with the correct precision as a replacement. – Jonathan Fite Mar 21 '19 at 12:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.