The same fields in most tables

Question

In the prototype database, I have a set of fields (such as name, description, status) that are required in several, functionally different tables.

These fields always have the same end-user functionality for marking, displaying, searching, filtering, etc. They are not part of the foreign key constraint. How should this be modeled?

I can think of the following options:

• Each table receives all of these attributes. In this case, what would you call them? The same in each table or with a table name prefix (e.g. usrName, prodName)

• Move them to the Attributes table, add the foreign key to the "main" tables, referring to Attributes.PK

• As stated above, but instead of the foreign key, use Attributes.PK as PK in the corresponding table of the main code.

Answer 1

it looks like you are thinking too much about normalization. remember, this is the idea that you reduce data redundancy. your example seems to indicate that you are worried about the "redundancy" in the meta-information of your database project.

ultimately, although user.name and user.description are functional, different from product.name and product.description , and should be considered as such. for status , it depends on what you mean by that. status the product / user indicator indicator only active? if so, then it makes sense to split it into another table.

using the information you provided, if "active / expired / deleted" is just an indication of the state in the database, then I definitely agree with this table structure:

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

however, if status could be changed to represent something semantically different (i.e. for users who are probably “active / retired / fired”, I would suggest splitting it up to future proof of design:

 user_status product_status id id name name 

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

Answer 2

If you do not use the same name or description values ​​in tables, you should not normalize this data. Type types are usually reused, so they are normalized. 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

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

If you have fields such as state (which means state inside the country), then there may be a path to a lookup table such as "State" with (id, short_name, long_name, etc.), then each record that refers the state needs only the state_id column, which, as you mentioned, is a reference to an entry in the state table.

However, in some cases, normalization of all data is not necessarily required, as this simply complicates the situation, but it should be obvious where to do it and where not to do it.

Hope this helps.

Answer 4

I would give each table its own set of columns, even if they have the same name and are logically similar.

If you ever need to change one of the tables by adding or removing some of these columns or changing their data type, you can only do this in the table where it exists, instead of understanding how to complicate the sharing of the attribute table.

Providing each control with its own attribute tables contributes to Cohesion , which is good. He also avoids your question about the direction in which foreign keys go.

Regarding column names, it is not necessary or appropriate to place prefixes for column names. If you ever make a join that causes columns with the same name to come from two tables, use them to distinguish them.

Answer 5

I always gave each table a 3-letter code, which I then use in all field names. Thus, in the product table there is prdname, prddescription, prdstatus, and in the supplier file I have venname, vendescription, venstatus. When everything comes together, there is no need to worry about the same named fields.

Of course, all tables have a field called plain old id , and in the product table there will be a field called venid that refers to the id field in the vendor table. In this case, I did not put the prd prefix on it, because the vened has a great meaning and is unambiguous.