Database table structuring

Question

I am a little confused about how I would restructure my database, currently I am engaged with three tables:

1. Items: which contains the basic information of an "item" (user.id, name, description, feature_1(bool), feature_2(bool) .. etc)

2. Feature_1: Which contains some optional values for an "item", so if the user decides to have Feature 1 to his item we it will create a record in Feature_1 and fill in those values. 1 item cannot have more than one record in "Feature_1" table so either it has 1 record or none.

3. Feature_2: Same as Feature_1 but has different optional values, so the user could add Feature_1 to his Item and/or Feature_2. Again each item can have 0 or 1 Feature_2 record.

I am not sure if it is the best practice or not, one of the options was mashing all values in 1 table "Items" with many NULL values.

Any advice and pointers will be greatly appreciated.

Answer 1

Separating out values like this can be helpful in terms of dealing with locking issues. If you have multiple daemons/scripts running that are updating and those tables and holding locks on them, it can be an issue to keep all those values together on one table. Of course, now that the values for the 'item' are separated on to three tables, you are going to have issues with chasing them down if/when you need all of them. Generally it is fine to keep these tables split up like this.

Additional, before anyone goes down the road of splitting data like this, it is generally better to try/exhaust each of the following:

Use 'with (nolock)' as much as possible to prevent locking issues where you can.

Modify the scripts that maintain this data to use 'begin tran' type transactions as little as possible.

Modify the execution of the scripts that maintain the data so they are more likely to be called sequentially than concurrently. e.g. put them in the same job.

Answer 2

I would tend to agree with the three tables. Because you say the items can only be linked to zero or one Feature_1 and zero or one Feature_2, you could add two columns to the item table with the reference to Feature_1 and Feature_2 primary keys. You can use foreign key constraints on those columns and make them null-able to indicate the feature isn't present. This also means that you can drop the booleans and keep the benefit of checking whether a type 1 or 2 feature is available without joining tables.

Answer 3

There is no one-size-fits-all solution to these types of problems. But you have described two very common, and appropriate, solutions.

In theory, you can actually remove the feature_x Boolean fields from the main table. That 'fact' is implicit in the existence, or not, of a record in the feature tables. Having them isn't bad, it's a type of caching/denormalisation that increases complexity in order to potentially increase performance.

The only time I may potentially avoid this set-up is when users can create new features, or you have an unfeasibly large number of features. Then you may want an Entity-Attribute-Value setup. EAV is extremely flexible, but has significant problems of it's own. Again, no one-size-fits all.

In short, if what you have is working for you, what you have seems fine.

Answer 4

Purists would say you should have each of the Features as separate tables, each with a foreign key to the Items table. And, generally, I'd agree, although if a fairly small percentage of items won't have both features, and the size of the feature records are small, it might be worth eliminating the overhead of having to join the tables.