Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-12-09 : 07:21:43
|
| Geoff Davis writes "I am wanting to write a hobbyist catalogue collection database in SQL Server 2005 Express and I am really struggling with its design so I would really appreciate some help or pointers.Want I want to do is have a single database store items of Music, Movies, Books, etc; what I call Collections but my design should not be limited to these collections, I could have anything really, Electrical goods for example.There can be many formats within a collection (e.g. Music can have formats of CD, DVD, T-Shirt, etc) but each format within a collection can have it's own set of attributes. (I am calling it an attribute but it might be known as something else) e.g. A Music CD has Artists (Artist Table Lookup) and a Track Listing (Track Listing Table) associated with it but a T-Shirt has completely different attributes such as Artists (Artist Table Lookup), Size (Size Table Lookup) and Colour (Colour Table Lookup) associated with it. So Size and Colour is not applicable to a Music CD but it is to a T-Shirt.It is fair to say that these attributes could be lookups tables as stated above, single values or even a list of fixed values. e.g. A Music CD has a label which is a lookup but a Photo will not doesn't have a label.Whats even more confusing for me is that a Movie CD has completely different attributes associated with it, it doesn't have Artists, it has Authors which even though is a person it is a different entity (Well I think it is) An Artist in the Music collection could be a person but it could also be a group of persons under a group name... this is making my head hurt!!!Regardless of the different collections and the different attributes associated with them I will need to be able to search on any field in the whole database, hmmm How do I do that?I did think of keeping the collections separate in my database, i.e. MusicArtist, MusicFormat, MusicItem and then have MovieAuthor, MovieFormat, MovieItem. I could then have a GlobalItem to do the searching on but is this the right way to go or should I work harder to provide a single products table; but if I do that what about the different attributes for each format?I suppose what I am after is a design like http://www.play.com - If you are looking at Music CD's you get a Track Listing but if you are looking at a DVD, you don't. Or even a store/supermarket type of approach where each product is different and the attributes stored against it needs to be easily retrieved, i.e. lookups.Can you please help? What is this kind of design called anyway?Thanks in advance,Geoff." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
Database Design for Different Collections
