Home » SQL & PL/SQL » SQL & PL/SQL » database desing concepts
database desing concepts [message #206411] Wed, 29 November 2006 23:56 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i am trying to build an application for doctors, and am
trying to design a database for this....before that a
colleague has asked a doubt regd her database design,

i never took db desgin seriously until now, when
i referred to all the data modelling techniques, er diagrams,
normalization etc....

and came to following conclusion

many to may should be avoided, if we find many to may between
two entities....then include another table in between
which has the ids' (primary keys maybe) from these two tables
and they are foreign keys ...(IS THIS CORRECT?)

just wanted to know if i am thinking correct and if i am
going in the right direction....

my final solution for her regd her design is as follows

(based on my previous post in design answered by Frank and jrowbottom)


the entities are BAND ( a group of singers)

GENRE ( pop, rock etc)

BANDTRACK ( song...)

the relation between band and genre is many to many
between band and song, its one to many
between genre and song, its many to many

i was surprised to hear that a song can fall into 2 genres...

and after studying the concepts, and following your guidelines,

i suggested the following solution to her..

create table tbl_band(bandid number(4),bandname varchar2(10),band_username varchar2(20),
 constraint tbl_band_pk primary key(bandid))



create table tbl_genre(genreid number(4),genretitle varchar2(10),
         constraint genre_pk primary key(genreid))




create table tbl_bandtrack(bandtrackid number(4),bandid number(4),bandtrname varchar2(10),
               constraint mypk primary key(bandtrackid),
                constraint myfk foreign key(bandid) references tbl_band(bandid)
                )



since each bandtrack(song) can fall into more than one
genres (pop,rock etc), and vice versa..

hence another table creation

create table tbl_gb(genreid number(4),bandtrackid number(4),
constraint gbfk1 foreign key(genreid) references tbl_genre(genreid)
contraint gbfk2 foreign key(bandtrackid) references tbl_bandtrack(bandtrackid))




so that i can join like this to get desired data

   select b.bandname,s.bandtrackid,g.genreid
   from tbl_band b,tbl_bandtrack s,tbl_gb g
   where b.bandid=s.bandid and s.bandtrackid=g.bandtrackid
  and g.genreid=10




and get the desired data....all i want to know is

am i correct? is this the right way to think about

database design??

also i observed, by the time we clearly understand er diagrams,
there is no need to go into normalization...

Re: database desing concepts [message #206452 is a reply to message #206411] Thu, 30 November 2006 02:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The solution you've come up with is the only way to implement a Many-Many link in a RDB that I know of, so yes, thats a good solution.

If I were you, I'd start taking database design a lot more seriously - poor programers can be worked round, poor code can be rewritted, but a poor design will cripple your entire project.
Re: database desing concepts [message #206637 is a reply to message #206411] Thu, 30 November 2006 22:56 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member

hi jrowbottom
well thanks a lot for the suggestion, i would take it
more seriously..

so in that direction can u suggest me what to do so that I
can learn more with database design ?

i mean to say, did u come across any book wherein I can not
only learn database design concepts well, but also u know
there might be some exercises to solve....

cause i think learning is one thing, we can learn a lot of
things only when we solve problems....
Re: database desing concepts [message #206689 is a reply to message #206637] Fri, 01 December 2006 02:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Two books I would recommend are
Expert Oracle Database Architecture and Expert Oracle by Design
Re: database desing concepts [message #206699 is a reply to message #206411] Fri, 01 December 2006 02:51 Go to previous message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
thank you, i have expert one on one written by thomas kyte

these 2 books seems to be good too
Previous Topic: count() function doubt
Next Topic: materialized views
Goto Forum:
  


Current Time: Wed Dec 07 12:59:41 CST 2016

Total time taken to generate the page: 0.16153 seconds