Same fields in most tables

Question

In a database prototype, I have a set of fields (like name, description, status) that are required in multiple, functionally different tables.

These fields always have the same end user functionality for labeling, display, search, filtering etc. They are not part of a foreign key constraint. How should this be modeled?

I can think of the following variants:

• Each table gets all these attributes. In this case, how would you name them? The same, in each table, or with a table name prefix (like usrName, prodName)

• Move them into a table Attributes, add a foreign key to the "core" tables, referencing Attributes.PK

• As above, but instead of a foreign key, use the Attributes.PK as PK in the respective core table as well.

Answer 1

it sounds like you might be taking the idea of normalization a bit too far. remember, it's the idea that you're reducing redundancy in your data. your example seems to indicate you're worried about "redundancy" in the meta information of your database design.

ultimately though, user.name and user.description are functionality different from product.name and product.description, and should be treated as such. for status, it depends what you mean by that. is status just an indicator of a product/user's record being active or not? if so, then it could make sense to split that to a different table.

using the info you provided, if "active/expired/deleted" is merely an indication of state within the database, then i'd definitely agree with a table structure like so:

users            products         status
  id               id               id
  name             name             name
  description      description
  status_id        status_id

however, if status could conceivably be altered to represent something semantically different (ie, for users, perhaps "active/retired/fired", i'd suggest splitting that up to future proof the design:

user_status     product_status
  id              id
  name            name

in short, normalize your data, not your database design.

Answer 2

Unless you use the same name or description values across tables, you shouldn't normalize that data. Status types tend to be reused, so, normalize those. For example:

order_status_types
- id
- name
- description

shipping_accounts
- id
- name
- description

orders
- order_status_type_id
- shipping_account_id

preferences
- shipping_account_id

Answer 3

Normalisation is often best practice in any relational database (within reason).

If you have fields like state (meaning the state within a country), then a reference table like "State" with (id, short_name, long_name etc...) might be the way to go, then each record that references a state only need a state_id column which, as you did mention, is a reference to a record in the State table.

However, in some instances normalisation of all data is not necessarily required as it just complicates things, but it should be obvious where to do it and where not to do it.

Hope this helps.