CODEWITHSUNDEEP
phpmysqldatabase
Hendry H.
Hendry H.

Reputation: 1520

database design : join with uid or with two fields

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 '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);

uid in method2 already has the information of data_type.

I kind of confuse which is best practice :
Method1 seems to be faster but need to specify ad_type = 'SALE';
Method2 seems to be simpler, only need to join with uid, but seems slower ? Is it true ?

Which one is best practice ? And which is better performance ? Or no different at all ?
PS. I normalize table ads because it will be joined with table house, table land, table newhousing, etc. Table ads will store ads_start_date, ads_end_date, and other usefull information.

Upvotes: 4

Views: 156

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

If you have an auto-incremented id in a table, I would strongly suggest 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 general rule, I favor the first method. The auto-incremented primary key has no additional information, such as an embedded ad_type code. In the second method, the thing called "uid" (which I think should really be the "user id") is serving two purposes. It is trying to be a unique key and it is trying to encode the type information.

I strongly favor having the type information as an explicit column.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108796

The most useful way to approach a design question like this is to consider whether your application needs to be able to handle all your ads -- whether for sales, rent, land, etc., -- together. If you need to do that, your first alternative is the best choice.

If it makes more sense to put ads for land, ads for rental properties, etc into their own tables, your second alternative is best. It seems you've already done that kind of thing with your house table. But that's my guess.

It is not best practice to JOIN on uuids when you can just as easily join on ordinary autoincrementing id columns. Using a uuid as a surrogate unique key when you have a perfectly good 'id unique key is just extra work and storage.

It is best practice to name your id columns -- your primary key columns -- uniformly. That is, use house.house_id in the house table and ads.house_id in the ads table. It's easier to read and inspect your SQL code when you do that.

Upvotes: 1

Related Questions