How to See the Structure of Mulitple Table with a Single "Desc"

How can I describe all tables in the database through one statement?

There is no statement that describe all tables at once. But you may want to do something like this :

SELECT * FROM information_schema.columns WHERE table_schema = 'db_name';

Single table against multiple tables for similar type of data

Much simpler to link in both types in the join condition:

left join data_types dt 
on dt.identifier = u.user_identifier and dt.typetype in ('usertype', 'roletype')

I don't think there are performance implications for the single table approach. The question is what makes for more readable queries.

Database Design: Multiple tables vs a single table

It seems that you already know the answer, but remember, keep the systems you design simple to modify as business models always change over time or they eventually fail (it's a generalization but you get the idea). A corollary of that is if you make a rigid model, fast or slow, it's rigid, changes will be harder and the end user won't see the difference, hence no money/happiness change is achieved, unless it's a very bad change.
Your problem is not technical in a way a query works on the engine but more of a philosophical one, easy changes versus apparent speed.
Ask yourself, what's the advantage of having a normalized database? Think about a clean architecture and design, performance is the least problem in todays world as processing is cheaper and storage also. But design is expensive.
Normalization was made to make systems that don't depend on last moment decisions but on a structured design process.
Big tables are not a big deal for MySql but they are a big deal to maintain, modify and expand. It's not just adding one more column, it's about the rigid structure of the data itself. Eventually in time you will just add columns that contain indexes, and those indexes will be pointing to small tables. MySql will be plowing it's way around all that data anyway.
So i'll go for the first one, a lot of small tables, many-to-many.

Get data and sort from MySQL two tables

If I got your question right, you can use union like this -

select * from table_1 union select * from table_2 order by create_date desc

EDIT

Create a view like this -

create view table_1And2 as select * from table_1 union select * from table_2

table_1And2 is not a good name, give a meaningful name.

And modify your long query like this -

SELECT 
table_1And2.id, 
table_1And2.created, 
table_1And2.qty, 
table_1And2.comments, 
table_1And2.last_update, 
table_7.firstname, 
SUBSTRING(table_7.lastname, 1, 1) AS lastname, 
table_8.title country, 
table_3.value AS sim, 
table_1And2.offer_request, 
table_5.manufacturer AS manufacturer, 
table_4.name AS model, 
table_6.value AS specifications, 
table_9.value as shipping, 
table_1And2.model AS mid, 
table_1And2.user_id, 
table_1And2.callme, 
table_1And2.phoneprice, 
table_1And2.phoneprice_eur, 
table_1And2.currency, 
table_1And2.sel_buy_show_all, 
table_1And2.seller_buyer
FROM (`table_1And2`)

LEFT JOIN `table_3` ON `table_3`.`id` = `table_1And2`.`sim`
LEFT JOIN `table_4` ON `table_4`.`id` = `table_1And2`.`model`
LEFT JOIN `table_5` ON `table_5`.`id` = `table_1And2`.`manufacturer`
LEFT JOIN `table_6` ON `table_6`.`id` = `table_1And2`.`specifications`
LEFT JOIN `table_7` ON `table_7`.`id` = `table_1And2`.`user_id`
LEFT JOIN `table_8` ON `table_7`.`country`=`table_8`.`id`
LEFT JOIN `table_9` ON `table_9`.`id` = `table_1And2`.`types`
WHERE `table_1And2`.`status` =  '1'
AND `table_1And2`.`deleted` =  '0'
ORDER BY `last_update` DESC
LIMIT 200

Delete from multiple tables using order by and limit

Doing it using a JOIN against a subselect to get the highest id from the my_rel_table

DELETE my_rel_table, my_photo_table
FROM my_rel_table 
INNER JOIN 
(
    SELECT MAX(id) AS MaxId
    FROM my_rel_table 
    WHERE relid = 1 
    AND type = 1
) Sub1
ON my_rel_table.id = Sub1.MaxId
LEFT OUTER JOIN my_photo_table ON my_photo_table.typeid = my_rel_table.typeid
WHERE my_rel_table.relid = 1 
AND my_rel_table.type = 1

Not directly tested as I have no test data!

EDIT - Couple of attempts to do the top 5, but again not tested

DELETE my_rel_table, my_photo_table
FROM my_rel_table 
INNER JOIN 
(
    SELECT id
    FROM my_rel_table 
    WHERE relid = 1 
    AND type = 1
    ORDER BY id DESC
    LIMIT 5
) Sub1
ON my_rel_table.id = Sub1.id
LEFT OUTER JOIN my_photo_table ON my_photo_table.typeid = my_rel_table.typeid
WHERE my_rel_table.relid = 1 
AND my_rel_table.type = 1

Or a different way.

DELETE my_rel_table, my_photo_table
FROM my_rel_table 
INNER JOIN 
(
    SELECT id, @Counter:=@Counter+1 AS ItemCounter
    FROM my_rel_table 
    CROSS JOIN (SELECT @Counter:=0) Sub1
    WHERE relid = 1 
    AND type = 1
    ORDER BY id DESC
) Sub1
ON my_rel_table.id = Sub1.id
AND Sub1.ItemCounter <= 5
LEFT OUTER JOIN my_photo_table ON my_photo_table.typeid = my_rel_table.typeid
WHERE my_rel_table.relid = 1 
AND my_rel_table.type = 1

Mapping one entity to multiple tables

I tried doing the same thing when I first started using nHibernate and couldn't find a way to do it. I actually don't believe that you can map multiple tables to a single object. Usually you would have one entity per table. Each entity will be mapped to their table and would have references/hasmany links between them.

You'll probably find that having one entity per table is better in the long run as well because it allows for simpler mapping to the database.

---

Related Topics