Database design: combining with uid or two fields

Question

Method 1:

CREATE TABLE `ads` ( `idads` int(11) NOT NULL AUTO_INCREMENT, `idobject` int(11) NOT NULL, `ad_type` enum('SALE','RENT','NEWHOUSING','GBUY','LAND','FIXMOVE') DEFAULT 'SALE', ) CREATE TABLE `house` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` varchar(15) DEFAULT NULL, 

To select the 'SALE' data

 SELECT * FROM ads a JOIN house h on (h.id = a.idobject) WHERE a.ad_type = 'SALE'; 

Method 2

 CREATE TABLE `ads` ( `idads` int(11) NOT NULL AUTO_INCREMENT, `uid` varchar(15), CREATE TABLE `house` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` varchar(15) DEFAULT NULL, 

TO SELECT 'SALE' data:

  SELECT * FROM ads a JOIN house h on (a.uid=h.uid); 

The uid in method2 already has information about the data_type type.

I am a little embarrassed that the best thing is:
Method1 seems faster, but you need to specify ad_type = 'SALE';
Method2 seems simpler, you just need to join the uid, but it seems slower? It's true?

Which one is best practice? What's the best performance? Or no difference at all?
PS. I am normalizing the bulletin board because it will be connected to a table, table, newhousing table, etc. The board will store ads_start_date, ads_end_date and other useful information.

Answer 1

The most useful way to approach design issues like this is to consider whether your application should handle all of your ads - be it sales, rentals, land, etc. - together. If you need to do this, your first alternative is the best choice.

If it makes sense to place ads for land, rentals, etc. in your own tables, it’s best to choose a second alternative. It seems you have already done such things with your house table. But this is my guess.

It is not recommended to use JUIN on uuids when you can just as easily join regular auto-increment id columns. Using uuid as a surrogate unique key when you have a perfectly good 'id unique key is just extra work and storage.

It is best to name the identifier columns — primary key columns — uniformly. That is, use house.house_id in the house table and ads.house_id in the ads table. It is easier to read and validate your SQL code when you do this.

Answer 2

If you have an auto-incremented id in the table, I highly recommend that you make it the primary key:

 CREATE TABLE `ads` ( `idads` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, ------------------------------------------^ `idobject` int(11) NOT NULL, `ad_type` enum('SALE','RENT','NEWHOUSING','GBUY','LAND','FIXMOVE') DEFAULT 'SALE', ); 

As a rule, I prefer the first method. The primary key with automatic addition does not have additional information, for example, ad_type embedded code. In the second method, a thing called "uid" (which, in my opinion, should really be a "user id") serves two purposes. It tries to be a unique key and tries to encode type information.

I highly recommend having type information as an explicit column.