 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Creating a database
I have a database in Access to keep records for CDs. The tables are as below Artist ArtID - 1,2,3... ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai... Genre GenID - 1,2,3.... Genre - Pop, Rock, Hip Hop, Jazz... CDType CDTypeID - 1,2,3... CDType - Album CD, Single CD, Double Cd, limited edition... MainCDInformation LineID - 1,2,3.... Artist - Linked with ArtID CDTitle - Name of Album CDType - linked with CDTypeID Genre - Linked with GenID Cost - User can enter a cost Notes - User can enter addtional notes I am getting conflicting advise whether this approach is correct or not the way this database has been designed. Would you guys agree to it or not? If not how would you create it and what rules would you follow? Thanks in advance >I have a database in Access to keep records for CDs. The tables are as >below [quoted text clipped - 24 lines] > way this database has been designed. Would you guys agree to it or not? If > not how would you create it and what rules would you follow? "Correct" is defined by the user. No schema is ever perfect and there are frequently times when a schema is compromised in some way to achieve some objective. Does that make the schema incorrect? If it works for you and satisifies your requirements, why do you care if others think it is not "correct"? If you are getting advice about the schema that is useful or helps meet your requirements in some "better" way, then incorporate it. It would help to have some examples of this advice to offer meaningful ... umm ... advice. In any event, a more complete definition of the tables (and how they are intended to be used) is required before anyone can make useful suggestions. If you want comments: * You have focused on a single medium (CD vs DVD, tape, vinyl, etc.). * You appear to have overlooked the fact that an album may have multiple artists or that an album may have no actual designated artist (insert late night party-time CD commercial reference here). * Notes? Your system supports a single user? * You appear to be focused on music only. What about books or other types of content? * Is it not possible to have a limited edition double CD? CDType seems to be a confusion of mutiple attributes. If you really want to go nuts, then go visit some audiophile sites - you are just scratching the surface of music content information. It may help to explore some online music sites to see what information they provide, how they organize, and how they link (or reference) related information. Alternatively, have you considered purchasing this type of software - there must be hundreds (if not thousands) of music cataloging programs (assuming that is the purpose of this schema). Thanks for your advise. Sorry for the basic format of the table. This was merely an idea to give you guys rather than having a lengthy page with tables and fields. I agree with what you have wrote but the issue is in Access this database is acceptable. Only 1 user enters the details. For me to retrieve information lets say about the Artist and the album title i would have a query like Artist.ArtistName with CDTitle.MainCDInformation (After the "." is the table name). Now this approach works. When I upgrade this DB to SQL 2005 - all ok here too. The problem occurs when i create an application in ASP .Net as the TableAdapter Preview looks correct but when generating the same information (Artist and Cd Title) and then running this application I have some data missing from fields that are joined to other tables and cant figure out why. Hope this makes some sense if not please let me know. If you wish i can post a snippet of the SQL code it generates. Thanks > >I have a database in Access to keep records for CDs. The tables are as > >below [quoted text clipped - 55 lines] > must be hundreds (if not thousands) of music cataloging programs (assuming > that is the purpose of this schema). > (After the "." is the table name). Now this approach works. When I upgrade > this DB to SQL 2005 - all ok here too. The problem occurs when i create an [quoted text clipped - 7 lines] > post > a snippet of the SQL code it generates. Not really. You've gone from a relatively simply schema question to an Access->SQL Server/ASP migration issue. If the actual schema is not an issue (i.e., it stores what you need and you can get the information you want from it), then you should probably post details of the actual problem to a NG that is focused on ASP. I thought it wouldnt which is why i wanted to keep this a simple question. However now you can understand why i was questioning if the DB structure i had was correct or not and why some say it was incorrect (since it doesnt show up correctly when the application runs but does otherwise). I have already asked in an ASP .Net group, which is why i asked here to see if someone could shed some light here or not :-). > > (After the "." is the table name). Now this approach works. When I upgrade > > this DB to SQL 2005 - all ok here too. The problem occurs when i create an [quoted text clipped - 13 lines] > want from it), then you should probably post details of the actual problem > to a NG that is focused on ASP. >I thought it wouldnt which is why i wanted to keep this a simple question. > However now you can understand why i was questioning if the DB structure i > had was correct or not and why some say it was incorrect (since it doesnt > show up correctly when the application runs but does otherwise). IMO, the clue here is that it "doesnt show up correctly when the application runs but does otherwise". I think you should give some serious thought to the code you are using and, to a lesser degree, the queries it generates to access the database. In your previous post, you also mentioned "joining to other tables". This also leads me to believe the issue is with the code, not the schema. The information you previously posted wasn't a complete schema, so perhaps you left out things needed for your application to work "correctly" (like primary keys, foreign keys, other tables, etc.). I had a feeling you might have said its code related as soon as i clicked Post on my last response. Its definately not the code as other DB works fine using this code also i tested the code which was provided with ASP .Net tutorials. I can post a detailed database structure if you think it could be something else. Its a tough one as thsi DB works fine with Access - the code works fine with other databases and when i generate the code under the TableAdapter i can view the results and they show up correctly (A TableAdapter is a bit like creating a query in design mode in SQL - sorry i added this incase you wasnt aware of what a TableAdapter is - if you do, my apologies) Thanks > >I thought it wouldnt which is why i wanted to keep this a simple question. > > However now you can understand why i was questioning if the DB structure i [quoted text clipped - 9 lines] > schema, so perhaps you left out things needed for your application to work > "correctly" (like primary keys, foreign keys, other tables, etc.). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.