urgent mySQL/entity relationships
hi, I would like some help on the following.
this is sort of a linux question, as mySQL is a linux thing :-D
In my exam soon, I am making a database, I need to make some enterprise rules for this description we've been given
I've worked them out to be:
each protocol has many forms of representation but only one form per protocol
(each protocol has a variable length)
each protocol is either local, regional, national or international
each protocol is written by one or more authors
each protocol has one date of production and date last reviewed and date next review
each protocol has 0 or more registered users
each protocol has 0 or more comments
each protocol has 0 or more hallmarks
each user has one username
each user has one password
each user has an address
each user has 0 or more protocols of interest
They might be a little basic- full details are here http://www.galassify.org/teaching/dmds
Therefore I am going to have tables like so:
user table
-----------------------------------------------
username|password|address|registered protocols|
-----------------------------------------------
here the username is the key because it will be unique to every user
address could also be a key, but it will be no good as people will have to log in, and
you never log in using your address
protocol table
-----------------------------------------------------------------
title|ref number|category|author|date of.....(3 fields)|comments|
-----------------------------------------------------------------
because there maybe more than one autor per protocol or more than one comment per protocol,
QUESTIONS:
1.do i therefore need 2 more tables
autor/comment table
---------------------------------
author/comment|reference number |
----------------------------------
which are linked directly to the autor/comment fields in the protocol table- is this possible?
If not, how else should it be done?
2. Is the entity relationship diagram like this?
USER is registered to ------> PROTOCOLS <---has one or more AUTHOR
one or more title
username* refnumber*
password keywords/category/dates etc
address
The reference number is not really a good key- as there is going to be one or more comments/categories/autors per reference number
I think it is the best way of doing it though.
What do you think?
thanks in advance
George
this is sort of a linux question, as mySQL is a linux thing :-D
In my exam soon, I am making a database, I need to make some enterprise rules for this description we've been given
I've worked them out to be:
each protocol has many forms of representation but only one form per protocol
(each protocol has a variable length)
each protocol is either local, regional, national or international
each protocol is written by one or more authors
each protocol has one date of production and date last reviewed and date next review
each protocol has 0 or more registered users
each protocol has 0 or more comments
each protocol has 0 or more hallmarks
each user has one username
each user has one password
each user has an address
each user has 0 or more protocols of interest
They might be a little basic- full details are here http://www.galassify.org/teaching/dmds
Therefore I am going to have tables like so:
user table
-----------------------------------------------
username|password|address|registered protocols|
-----------------------------------------------
here the username is the key because it will be unique to every user
address could also be a key, but it will be no good as people will have to log in, and
you never log in using your address
protocol table
-----------------------------------------------------------------
title|ref number|category|author|date of.....(3 fields)|comments|
-----------------------------------------------------------------
because there maybe more than one autor per protocol or more than one comment per protocol,
QUESTIONS:
1.do i therefore need 2 more tables
autor/comment table
---------------------------------
author/comment|reference number |
----------------------------------
which are linked directly to the autor/comment fields in the protocol table- is this possible?
If not, how else should it be done?
2. Is the entity relationship diagram like this?
USER is registered to ------> PROTOCOLS <---has one or more AUTHOR
one or more title
username* refnumber*
password keywords/category/dates etc
address
The reference number is not really a good key- as there is going to be one or more comments/categories/autors per reference number
I think it is the best way of doing it though.
What do you think?
thanks in advance
George
