Database structure design with variable amounts of fields

For some kind of inventory system, a user can create a item_type which has certain properties, now the amount of properties is varia开发者_如何学Pythonble per type.. How should I go with the DBStructure on this? Just make a X amount of propertyX fields, or is there some other way to achieve flexibility in this way?

note I don't want to automaticly create tables, since that ain't managable.

The usual way to do this is like this (pseudo-SQL):

create table property_types (
  property_id int primary key,
  name varchar,
  -- more info here
);

create table items (
  item_id int primary key,
  -- your item table
);

-- this table links a property value with an item
create table item_properties (
  item_id int,
  property_id int,
  property_value varchar,
  foreign key fk_item (item_id) references items (item_id),
  foreign key fk_property (property_id) references properties (property_id)
);

Optionally, you can have a unique constraint on item_properties (item_id, property_id) to ensure that every property is set only once per item

Have a property table which is linked n:1 to the item_type table like this:

TABLE item_type (
  item_type_id INT,
  ... )

TABLE properties (
  property_id INT, -- primary key
  item_type_id INT, -- foreign key for item_type
  key NVARCHAR(max), -- adjust type to your needs
  value NVARCHAR(max) )

So each item_type can have 0:n properties.

You could achieve a dynamic number of properties by creating a Properties table with each property referencing your Items table. e.g.

Items Table

Id  Name


Properties Table

Id  ItemId  Name  Value

In this manner, you're not limited to a static number of properties, or even the same properties. The Properties table would essentially be a dictionary table of name/value pairs with a strong tie to your Items table.