Creating 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"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
>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?
"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
"Scott Morris" wrote:
> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
> >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?
> "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).
>
>|||> (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.
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 :-).
"Scott Morris" wrote:
> > (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.
> 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.
>
>|||"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
>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
"Scott Morris" wrote:
> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
> >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.).
>
>