relational database design, one meta table for several tables?

0
votes

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...)?

1
mysqldatabasedatabase-designrelational-database
one meta table? I wouldn't mix the data from user (phone numbers) with the data from products (colors/materials) ... it doesn't make any sense – nacho
something like meta_id(for user_id,shop_id,product_id), meta_key(user_phone_number) and meta_value(12345) and then select meta_value from meta_table were meta_key=user_phone_number and meta_id=user_id. – the110boy
You are not clear. What do you mean, "meta table"? Please give details of your tables & colums, queries and example data. Please edit your question, comments are not for clarifications. Read an intro to information modeling & database design, and read about problems with EAV which essentially stores metadata & data instead of data that the DBMS can actually manage. – philipxy

1 Answers

1
votes

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).