CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-schema
Ankit
Ankit

Reputation: 1105

normalization of database structure

I was reading the concept of normalization of database structure. I got confused with the following situation in my project.

  1. I have two tables "TableA" and TableB
  2. Both tables are independent of each other and have no realtionship at all
  3. They represent completely different data
  4. Both the tables will have different parameters. However Parameter itself as an object has same properties.

So my concern is should I have single Parameter table which is serving TableA and TableB both

Or

I should have separate Parameter Table for both Table A and Table B

Structure look likes this

Case I:

TableA
ID
Name
Description

TableB
ID
Name
SomeFlag

Parameter
ID
TableA_ID
TableB_ID
Name 
Description
Type

Case II

TableA
ID
Name
Description

Parameter_A
ID
TableA_ID
Name 
Description
Type

TableB
ID
Name
SomeFlag

Parameter_B
ID
TableB_ID
Name 
Description
Type

I personally prefer Case I, as it does make sense to create another table representing same type of data.

As per normalization's concept we should have a table that represents only one thing. So i guess i should have only one parameter table. But what if that table mean something completely different when viewed from TableA and different when viewed from TableB?

Upvotes: 0

Views: 67

Answers (2)

Gary_W
Gary_W

Reputation: 10360

I would use case one but with some changes. The parameter entity does hold one thing, parameters for a table. An instance of a parameter entry should relate to only one table (based on your analysis that they are not related). 

Parameter
----------
PK Param_ID 
FK Main_Table_ID 
Main_Table_name (A or B)
param_Name 
param_Description
param_Type

Upvotes: 2

Greg
Greg

Reputation: 424

If it makes logical sense for a Parameter to have both Table A and Table B in the same instance (not an either/or), then Case I is better.

In Relational Theory, every table is a type. Even if they may have common data, types are based around their usage. And though it's a little more complicated, Case II is more normalized.

There is another possibiltiy, that hasn't been mentioned, I'll call it Case III.

TableA
ID
Name
Description
PropertyID

TableB
ID
Name
SomeFlag
PropertyID

Parameter
ID
Name 
Description
Type

If the Properties will always be common among both tables, this is probably going to be the best solution.

Upvotes: 0

Related Questions