how to find relationships in mySql database

On 21 May, 13:37, JRough wrote:
> I need to analyze a MySQL database. I know how to normalize a
> database but don't know MySQL administration. We have the phpAdmin
> and the database is stored on a server off site. All I can see there
> is the tables. How do I find the data dictionary or the key fields and
> constraints?
>
> I would like to document the system eventually but now there are
> problems with users of the interface and they want additional
> features. I read about DAtaArchitect? Can anyone recommend it or
> should I just use Visio to document the ERD? Are there other tools.
>
> thanks,

phpMyAdmin (PMA) has functionality to document these relationships,
but it needs to have been configured.

If you click on the table structure link, PMA will show you the full
details of the table (datatypes, indexes, keys, ..)

Unless you are using the correct database engine (e.g. InnoDB),
foreign key constraints are not honoured. Software like DBDesigner
will produce a nice ERD, but at a base level, you could ask PMA to
export all the CREATE TABLE statements which will show you all the
information that you are asking about.

JRough wrote:
> I need to analyze a MySQL database. I know how to normalize a
> database but don't know MySQL administration. We have the phpAdmin
> and the database is stored on a server off site. All I can see there
> is the tables. How do I find the data dictionary or the key fields and
> constraints?

INFORMATION_SCHEMA holds the answers to most of your questions.


> I would like to document the system eventually but now there are
> problems with users of the interface and they want additional
> features. I read about DAtaArchitect? Can anyone recommend it or
> should I just use Visio to document the ERD? Are there other tools.

Never needed outside programs to document a database & relations, can't
help you there.

--
Rik Wasmus
....spamrun finished

On May 21, 6:43 am, Captain Paralytic wrote:
> On 21 May, 13:37, JRough wrote:
>
> > I need to analyze a MySQL database. I know how to normalize a
> > database but don't know MySQL administration. We have the phpAdmin
> > and the database is stored on a server off site. All I can see there
> > is the tables. How do I find the data dictionary or the key fields and
> > constraints?
>
> > I would like to document the system eventually but now there are
> > problems with users of the interface and they want additional
> > features. I read about DAtaArchitect? Can anyone recommend it or
> > should I just use Visio to document the ERD? Are there other tools.
>
> > thanks,
>
> phpMyAdmin (PMA) has functionality to document these relationships,
> but it needs to have been configured.
>
> If you click on the table structure link, PMA will show you the full
> details of the table (datatypes, indexes, keys, ..)
>
> Unless you are using the correct database engine (e.g. InnoDB),
> foreign key constraints are not honoured. Software like DBDesigner
> will produce a nice ERD, but at a base level, you could ask PMA to
> export all the CREATE TABLE statements which will show you all the
> information that you are asking about.

Okay thanks, I got in phpAdmin/structure for all the tables. I see
the key fields and indexes :-)

In export, There is a choice of where to export it to, I guess a pdf
or Word in order to document it.

In order to export all the CREATE TAble statements do I have to go in
to each table separately and do the export?
If I go to the database level instead of the indidvidual table level
and then go in SQL view I don't see any SQL statements to export.
Otherwise if I am at the table level it looks like you can only
export one SQL statement at a time?




How do you tell if it has the correct database engine InnoDB? I don't
see that referenced anywhere.
Thanks for your help. First time in PHPAdmin

On May 21, 7:09 am, Rik Wasmus wrote:
> JRough wrote:
> > I need to analyze a MySQL database. I know how to normalize a
> > database but don't know MySQL administration. We have the phpAdmin
> > and the database is stored on a server off site. All I can see there
> > is the tables. How do I find the data dictionary or the key fields and
> > constraints?
>
> INFORMATION_SCHEMA holds the answers to most of your questions.
>
> > I would like to document the system eventually but now there are
> > problems with users of the interface and they want additional
> > features. I read about DAtaArchitect? Can anyone recommend it or
> > should I just use Visio to document the ERD? Are there other tools.
>
> Never needed outside programs to document a database & relations, can't
> help you there.
>
> --
> Rik Wasmus
> ...spamrun finished

Hi Rik
Many thanks,

I put this query in the SQL tab in PHPAdmin on one of my tables and
got the error below:

SELECT INTERNAL_BILLING_CYCLE,
DATA ,
ENGINE
FROM INTERNAL_BILLING_CYCLE
WHERE table_schema = 'INTERNAL_BILLING_CYCLE'
ORDER BY table_name DESC
LIMIT 0 , 30

MySQL said: Documentation
#1054 - Unknown column 'INTERNAL_BILLING_CYCLE' in 'field list'

I don't know what the database engine type is and I guessed that the
table type is data.
Are table type and engine type optional or default values?

Can I put all the table names in the SELECT at the database level or
do I have to do it for each table?

thanks again,

On May 21, 7:09 am, Rik Wasmus wrote:
> JRough wrote:
> > I need to analyze a MySQL database. I know how to normalize a
> > database but don't know MySQL administration. We have the phpAdmin
> > and the database is stored on a server off site. All I can see there
> > is the tables. How do I find the data dictionary or the key fields and
> > constraints?
>
> INFORMATION_SCHEMA holds the answers to most of your questions.
>
> > I would like to document the system eventually but now there are
> > problems with users of the interface and they want additional
> > features. I read about DAtaArchitect? Can anyone recommend it or
> > should I just use Visio to document the ERD? Are there other tools.
>
> Never needed outside programs to document a database & relations, can't
> help you there.
>
> --
> Rik Wasmus
> ...spamrun finished

Rik
I did this query in phpAdmin in SQL tab to find info from
information_schema:

SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName'
ORDER BY table_name DESC

I got an empty set result. I'm not sure I have the query right. I
put my database name in the WHERE clause.
I don't know if I'm supposed to change any of the other values to get
the schema on all the tables.

tia,
janis

On May 21, 7:09 am, Rik Wasmus wrote:
> JRough wrote:
> > I need to analyze a MySQL database. I know how to normalize a
> > database but don't know MySQL administration. We have the phpAdmin
> > and the database is stored on a server off site. All I can see there
> > is the tables. How do I find the data dictionary or the key fields and
> > constraints?
>
> INFORMATION_SCHEMA holds the answers to most of your questions.
>
> > I would like to document the system eventually but now there are
> > problems with users of the interface and they want additional
> > features. I read about DAtaArchitect? Can anyone recommend it or
> > should I just use Visio to document the ERD? Are there other tools.
>
> Never needed outside programs to document a database & relations, can't
> help you there.
>
> --
> Rik Wasmus
> ...spamrun finished

Rik:
Many thanks,
I did the query below on the information_schema table. I got an
error.
I guessed the table type was 'data'. The database engine is InnoDB.
Does this give you schema on all the tables in database 'ridge'?
Do you know what the error in the query is? I did the query in the
SQL tab in
phpAdmin.

Error

SQL query: Documentation

SELECT table_name,
DATA , InnoDB
FROM information_schema.tables
WHERE table_schema = 'ridge'
ORDER BY table_name DESC
LIMIT 0 , 30

MySQL said: Documentation
#1054 - Unknown column 'data' in 'field list'

tia,
janis

Rik Wasmus
>

Rik, how many groups do you watch. You have helped me many times in
comp.lang.php, and I have seen you in alt.html and comp.lang.javascript.

Hmmm, maybe you could help me with the question I posted?

Seriously, thanks for all the help in the past.

--
Shelly

On Wed, 21 May 2008 20:47:20 +0200, sheldonlg wrote:
> Rik Wasmus
> >
>
> Rik, how many groups do you watch. You have helped me many times in
> comp.lang.php, and I have seen you in alt.html and comp.lang.javascript.

Well, just my stylesheets lurking (and smirking... pff layout is not for
me, stay away from it), and you've got nearly all english groups I
frequent :P

> Hmmm, maybe you could help me with the question I posted?

I think I just did, but this is terrible thread pollution of course.

> Seriously, thanks for all the help in the past.

I'm glad my want of a life is of service to you :)
--
Rik Wasmus
....spamrun finished