do not use "," separated column values, do something like the following instead:1. singers
singer_id
singer_name
(other info....)examples:
1 张雨声
2 刘德华2. albums (assume an album only belongs to one singer, otherwise, split the table into two)
album_id
album_name
singer_id examples:
1 专辑1 1
2 专辑2 23. songs
song_id
song_name
album_id
singer_id (in case there are songs which do not belong to any album)examples:
1 两个传说 1 1
2 没有传说 1 1
3 本小孩 2 2
4 马桶 2 24. songs_on_the_net
song_id
urlexamples:
1 http://www.xxx.com/lll.mp3
1 http://www.xxx.com/222.mp3
1 http://www.xxx.com/333.mp3
2 http://www.xxx.com/444.mp3
2 http://www.xxx.com/555.mp3
2 http://www.xxx.com/666.mp3
2 http://www.xxx.com/777.mp3
3 http://www.xxx.com/aaa.mp3
3 http://www.xxx.com/bbb.mp3
3 http://www.xxx.com/ccc.mp3
3 http://www.xxx.com/ddd.mp3
4 http://www.xxx.com/eee.mp3
4 http://www.xxx.com/fff.mp3
4 http://www.xxx.com/ggg.mp3
4 http://www.xxx.com/hhh.mp3the above table structure is good for updates, but if your data is mainly readonly and you need high search speed, denormalize the tables>>>>如何把mysql数据库导入倒mssql中呢?
Migrating MySQL to Microsoft SQL Server 2000
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/iis/deploy/depovg/MySQL.aspSQL Server FAQ: How can I migrate a MySQL database to SQL Server 2000
http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=70
singer_id
singer_name
(other info....)examples:
1 张雨声
2 刘德华2. albums (assume an album only belongs to one singer, otherwise, split the table into two)
album_id
album_name
singer_id examples:
1 专辑1 1
2 专辑2 23. songs
song_id
song_name
album_id
singer_id (in case there are songs which do not belong to any album)examples:
1 两个传说 1 1
2 没有传说 1 1
3 本小孩 2 2
4 马桶 2 24. songs_on_the_net
song_id
urlexamples:
1 http://www.xxx.com/lll.mp3
1 http://www.xxx.com/222.mp3
1 http://www.xxx.com/333.mp3
2 http://www.xxx.com/444.mp3
2 http://www.xxx.com/555.mp3
2 http://www.xxx.com/666.mp3
2 http://www.xxx.com/777.mp3
3 http://www.xxx.com/aaa.mp3
3 http://www.xxx.com/bbb.mp3
3 http://www.xxx.com/ccc.mp3
3 http://www.xxx.com/ddd.mp3
4 http://www.xxx.com/eee.mp3
4 http://www.xxx.com/fff.mp3
4 http://www.xxx.com/ggg.mp3
4 http://www.xxx.com/hhh.mp3the above table structure is good for updates, but if your data is mainly readonly and you need high search speed, denormalize the tables>>>>如何把mysql数据库导入倒mssql中呢?
Migrating MySQL to Microsoft SQL Server 2000
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/iis/deploy/depovg/MySQL.aspSQL Server FAQ: How can I migrate a MySQL database to SQL Server 2000
http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=70
declare @i int
declare @max int
set @i = 1
select @max = max(id) from b
while @i <= @max
begin
if exists (select * from #temp where artist = (select artist from b where id = @i) )
update #temp set id = A.id + ',' + cast(B.id as char) from #temp as A, b as B where A.artist = B.artist and B.id = @i
else
insert #temp select artist, cast(id as char) from b where b.id = @i
set @i = @i + 1
end
select * from #temp order by artist
drop table #temp
不过最后我看见一句话。
the above table structure is good for updates, but if your data is mainly readonly and you need high search speed, denormalize the tables
最后一个denormalize 没看懂。请问是否这个不适合大量的查询?因为这是这个库主要的作用了。
1,张雨声,两个传说,http://www.xxx.com/lll.mp3,专辑1
2,张雨声,两个传说,http://www.xxx.com/222.mp3,专辑1但这样更新起来比较麻烦,比如说你要把“刘德华”改成“刘思归”,如果你多个表里含有“刘德华” 的字样,你需要改动很多条数据 (当然如果你只有一个表的话,那就无所谓了)怎么设计好完全看你的数据量以及需求而定,如果你拿捏不准的话,最好是测试一下各种设计,选出一种比较合理的设计
这样的记录是没有办法查询的,规范化的设计是需要的,要考虑的只是规范化的程度,saucer上面的设计是符合3nf的,一般应该这么设计,建好索引联接的速度影响不是很大的,毕竟查询只是筛选部分数据。
可否继续讨论。谢谢。各位帮助。