mysql 以下のような検索ができるsql文はありますでしょうか？

Question

データ構造

# route
{
	"id": "kdfnakdnfakndfajfajdb",
	"name": "山手線"
}

# station
{
	"id": "nfwiha4hqwkefnjg",
	"route_id": "kdfnakdnfakndfajfajdb",
	"name": "渋谷駅"
}

# region
{
	"id": "faknfakndfa",
	"name": "港区"
}

# estate
{
	"id": "fnaqi3flanv",
	"name": "estate",
	"region_id": "faknfakndfa",
	"accesses": [
		{
			"route_id": "aknfdfadn",
			"station_id": "fadkfakdnfadfn",
			"during": 3
		},
		{
			"route_id": "kdfnakdnfakndfajfajdb",
			"station_id": "nfwiha4hqwkefnjg",
			"during": 19
		}
	]
}

# room

{
	"id": "asdfghjklsdfghjkl",
	"estate_id": "fnaqi3flanv",
	"floor": 3,
	"rent": 120000,
	"is_taken": false
}

質問

今上のようなデータ構造を考えていて、次のような条件で検索をしたいと思っています。

routeが山手線でstationが渋谷駅か恵比寿駅でrentが100000以上のroomをもつ

このような条件のもとestateを取得したいと思っています。

おそらくデータ構造も悪いのでご指摘いただけると幸いです。皆さんの知恵をお借りできれば幸いです。

Answer 1

estateがroute情報とstation情報を持っていますが
stationがroute情報をもっているのでestateのrouteはいらないのでは？
もちろん山手線の渋谷と東横線の渋谷を別管理したいということなら必要ですが

またestateが複数stattion情報をもつなら正規化する必要があります

sample

冗長に処理すればこんな感じ

• 元データ

create table route(ro_id varchar(100) primary key,ro_name varchar(30));
insert into route values("kdfnakdnfakndfajfajdb","山手線");

create table station(st_id varchar(100) primary key,ro_id varchar(100),st_name varchar(30));
insert into station values("nfwiha4hqwkefnjg","kdfnakdnfakndfajfajdb","渋谷駅");

create table region(re_id varchar(100) primary key,re_name varchar(30));
insert into region values("faknfakndfa","港区");

create table estate(es_id varchar(100) primary key,es_name varchar(30),re_id varchar(100));
insert into estate values("fnaqi3flanv","estate","faknfakndfa");

create table access(ac_id int primary key auto_increment,es_id varchar(100),ro_id varchar(100),st_id varchar(100),during int);
insert into access(es_id,ro_id,st_id,during) values("fnaqi3flanv","aknfdfadn","fadkfakdnfadfn",3),
("fnaqi3flanv","kdfnakdnfakndfajfajdb","nfwiha4hqwkefnjg",19);

create table room(rm_id varchar(100) primary key,es_id varchar(100),floor int,rent int,is_taken tinyint);
insert into room values("asdfghjklsdfghjkl","fnaqi3flanv",3,120000,0)

• 検索

select es.* from estate as es
inner join room as rm on es.es_id=rm.es_id and rm.rent>=100000
and rm.es_id in (
select es_id from access as ac where ac.st_id in(
select st_id from station as st
inner join route as ro on st.ro_id=ro.ro_id and ro.ro_name='山手線'
and st.st_name in('渋谷駅','恵比寿駅')
)
)

Comments

[sueken5]

ご回答ありがとうございます。

山手線の渋谷と東横線の渋谷を別管理したいのでestateにroute入れています。

正規化する場合はどのようにすべきなのしょうか？知恵を貸していただけると幸いです。

[sueken5]

ありがとうございます。非常に参考になりました。ベストアンサーにさせていただきます。