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 |
|
cDc
Starting Member
30 Posts |
 Posted - 2004-04-16 : 13:03:41
|
| Hi ThereI am working on a database design that will drive the back end for a system that needs to store data for mainly music items (CDs, Cassettes, Vinyl etc) but also other types of product. The core of this is a product table and my question relates to options on how to store the artist/title;- have seperate shorter length artist/title fields- have a single description field containing a concatenation of artist/title- have a single description field to store the title and then lookup the artist from source data for products where an artist can be found.one voice inside me says well as this table will store many types of products not just music, its not right to have a specific artist field also classical music does not have an artist per se. The other voice says well if you dont split it then it will be harder to maintain, search and sort and just live with the nulls for classical music & other product types.I realise there is not a deinifitve answer but wondering if anyone has any experience of this sort of dilemma! (also keep in mind the rowcount of this table will be +2mill). Im swaying towards seperate fields as I can always merge it if i see the need later but not neccessarily splitting it the other way. Argh - im sure im overanalyzing this :)Cheers! |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-16 : 14:00:43
|
| I'm not sure why you think this would be harder to maintain if you split it up.product-------product_idproduct_nameproduct_typemusic_information-----------------product_idmusic_typeartist_name (use this for the composer if classical)music_titlemusic_genrecar_information---------------product_idcar_makecar_modelcar_yearblah, blah, blah.To me it seems like it would be easier to maintain. The other thing is if you are only searching for music as an example, you never have to look at any of the other information. Your result set and table sizes are smaller. You can filter the products quickly and efficiently by type.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
cDc
Starting Member
30 Posts |
Posted - 2004-04-16 : 14:19:02
|
| HiYes see what your saying - I had something similar to this when I first designed the tables, it seemed like a lot of extra work to write the relevent back end maintenance code and having more complicated update routines when data gets inserted/updated weekly or whatever at the time and I re-designed it into one flat table. Maybe I need to rethink and test this as i know this is the correct way and ultimately most efficient and less costly in terms of storage and so on.Cheers! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-16 : 14:33:20
|
| Think of the future also. We had some developers that developed this "little lead application" in one big flat table. It's now two big flat tables because they ran out of column space. Everytime you pull one record from this table it's an entire page of memory.We now have a > 500,000 records in this database (hahaha) and it runs horrible, regardless of how well we tune it. What a fun system. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
DB design - Music Data