Database design for object attributes

Question

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?

score 1 · Answer 1 · answered Mar 21 '19 at 12:45

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.

Database design for object attributes

1 Answers1