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?
Answer :
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.