CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
the110boy
the110boy

Reputation: 71

relational database design, one meta table for several tables?

i do not know much about database design, recently i got a project that promises to need a huge database and i'm confused about what is the best way of designing an standard database structure(mysql).

for example let's say we have 3 tables: users,shops,products each of these(users,shops,products) may have one or several details, like social page links(for shops),phone numbers(for users), addresses(for shops and users), colors\materials(for products),etc...

should i create only one meta table and store all of the details in it or should i create separate meta tables for each table?(user_meta,shop_meta,product_meta). which one is a better approach?

any advice and suggestions will be greatly appreciated.

Edit

according to mr Branko Dimitrijevic every table should have it's own meta table.

my problem is that user may have several types of details like: social pages url, user addresses,user role, etc...

is it ok to design database like this:

**Table: USERS**

userId | username | email | pass


**Table: USERS_META**

userId | metaType | metaValue

metaType will be the different types of details user can have like address,instagram page,facebook page,etc...

for example records in USERS_META table would be like:

userId |   metaType   |       metaValue
---------------------------------------------------
2      |  address     | some address for user home
---------------------------------------------------
2      |  role        | 1(id of the role)
---------------------------------------------------
2      |  instagram   | instagram.com/userpage
---------------------------------------------------
3      |  address     | some address for user 3 home
---------------------------------------------------
3      |  facebook    | facebook.com/userpage
---------------------------------------------------
3      |  role        | 2(id of the role)
---------------------------------------------------
2      |  age         | 18
---------------------------------------------------
3      |  age         | 19

now if i want the address of user 2:

SELECT metaValue FROM USERS_META WHERE userId=2 AND metaType='adress'

and if i want users social pages link:

$array=array('facebook','instagram','telegram','etc');
SELECT metaValue FROM USERS_META WHERE userId=2 AND metaType IN($array)

is this a good way or should i create separate tables for each meta(USER_ADDRESSES,USER_SOCIAL,USER_AGE,etc...)?

Upvotes: 0

Views: 370

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

In a nutshell: you shouldn't "merge" multiple tables.

Even if they happen to have same fields, they almost certainly don't have same constraints (e.g. foreign keys pointing to them from other tables).

Upvotes: 1

Related Questions