Is mySQL supposed to be this confusing?

[fuzzybabybunny]

So I'm trying to use databases for my website. I installed MySQL, and for the life of me I can't figure it out. I did a little bit of Access DB building back in college but this is a whole different world.

1. No nice GUI like Access. Can't visually see and make relationships to tables. I'm using phpmyadmin.

2. I want to start by creating two tables: one for properties (EstateID primary key, street, city, zip, #baths, RealtorID, etc) and one for the realtor (RealtorID primary key, fname, lname, phone #, etc). I want to link the Estate table to the Realtor table using the RealtorID as a foreign key. Apparently foreign keys are not supported in the default MySQL table? Uhhhh... aren't foreign keys one of the foundations in ALL relational databases? It's relational. Different tables in a single database are linked through foreign keys and related to each other... I HAVE to be missing something...

Without foreign keys don't you just have a bunch of tables that can't be linked to each other?

 

[lyssword]

You should try microsoft sql express 2008, I have it (I believe it's the version with tools included) and it has SQL server management studio it has same nice GUI and visually see relationships as MS access. The only difference is express is limited to 4gb tables compared to full snorgle version.

 

[blahblah99]

You'd have to link it via an sql statement using LEFT, RIGHT, or MIDDLE JOIN.

It's not like MSSQL where you can create views and stored procedures.

MYSQL takes a little more programming to make full use of it, and as a result it OWNS mssql anytime of the week.

 

[fuzzybabybunny]

Originally posted by: blahblah99
You'd have to link it via an sql statement using LEFT, RIGHT, or MIDDLE JOIN.

It's not like MSSQL where you can create views and stored procedures.

MYSQL takes a little more programming to make full use of it, and as a result it OWNS mssql anytime of the week.

So for small websites should I just stick with Access because it's easier and faster to learn?

 

[Snapster]

Originally posted by: blahblah99
You'd have to link it via an sql statement using LEFT, RIGHT, or MIDDLE JOIN.

It's not like MSSQL where you can create views and stored procedures.

MYSQL takes a little more programming to make full use of it, and as a result it OWNS mssql anytime of the week.

Small websites are more suited to mysql, but soon as you move to multi-core and enterprise environments it gets spanked by oracle, postgresql and mssql and certainly lacks the features that one would expect in those environments.

Originally posted by: fuzzybabybunny

So for small websites should I just stick with Access because it's easier and faster to learn?

Very small you can get away with it, but I think it would be worth your while learning some mysql to progress your skills set for future websites.

 

[Red Squirrel]

Eww I would not use access for a website lol. You need to give yourself room to expand, and mysql will give you that and is pretty much standard for the web.

Mysql is actually fairly easy compared to other real databases like ms sql. I would hardly even classify access as a database, and i have no idea how well/bad it would do with multiple requests at once, probably get corrupted.

 

[WannaFly]

Take a look at a program called SQLYog - it's a windows app that lets you manage your mysql databases. Makes it much easier.

DON'T use access, it's not reliable enough for a website.

 

[Nothinman]

1. No nice GUI like Access. Can't visually see and make relationships to tables. I'm using phpmyadmin.

There are a few GUI admin tools for MySQL, although I haven't used them. You might even be able to use Access if you have the proper connectivity to the MySQL server and the MySQL ODBC driver installed and configured.

Apparently foreign keys are not supported in the default MySQL table? Uhhhh... aren't foreign keys one of the foundations in ALL relational databases? It's relational. Different tables in a single database are linked through foreign keys and related to each other... I HAVE to be missing something...

I'm not sure if MyISAM does FKs by default stuff like that was one of the reasons that InnoDB became the default table format in recent versions.

Without foreign keys don't you just have a bunch of tables that can't be linked to each other?

Pretty much. Without FKs you'll have to do all of the link verification on your own.

MYSQL takes a little more programming to make full use of it, and as a result it OWNS mssql anytime of the week.

Actually MSSQL is a damn good product and kills MySQL is a lot of areas. The only real area that MySQL has an advantage is in momentum because of the licensing. Well that and it runs on more platforms than MSSQL so it's an option whether the server runs Windows, Linux, FreeBSD, etc where as with MSSQL the host has to run Windows.

So for small websites should I just stick with Access because it's easier and faster to learn?

No, at best Access should be used for prototyping things. I can't imagine it being a good idea for anything in production.

 

[Markbnj]

MySQL has a suite of GUI tools. I have used them, but I don't usually do visual table design so I don't recall how strong they are in that area.

http://dev.mysql.com/downloads/gui-tools/5.0.html

I wouldn't use Access for any site that needs to be reliable and perform well. The key difference between Access and a real database engine is that Access doesn't have a database engine. It's just a file sitting in a location, potentially shared, and all the data manipulation is done on the client, which would be your webserver in this case.

Originally posted by: blahblah99
You'd have to link it via an sql statement using LEFT, RIGHT, or MIDDLE JOIN.

It's not like MSSQL where you can create views and stored procedures.

MYSQL takes a little more programming to make full use of it, and as a result it OWNS mssql anytime of the week.

MySQL doesn't support views? Or stored procedures? Maybe you thought OP was still talking about Access?

Fuzzy, I haven't worked on MySQL in almost a year, so I can't toss syntax at you, but generally the "foreign key-ness" of a column is not a property of the column, but rather of a constraint placed on the table. A foreign key constraint says that for table A with column B, B must reference a valid row in table C with column D, where A.B == C.D.

A GUI front-end might let you set the foreign key properties of a column, but what it is really doing is adding a CONSTRAINT clause to the table definition.

http://dev.mysql.com/doc/refma...n-key-constraints.html

 

[BoberFett]

Access is a single user database, so you can have locking issues on a high traffic site. A real RDBMS is far preferable. That said, I ran a fairly successful forum for several years (2002 to around 2006) on an Access database. 10,000+ members, 1,000+ members active daily, hundreds of thousands of hits per month. There were some performance issues at the end, but for a low traffic site Access isn't unworkable.

 

[Red Squirrel]

The only real use for access is for a single user db that you know wont get very big. If it's something that grows, then code a proper web based app and use mysql, or other db system.

 

[Crusty]

Originally posted by: RedSquirrel
The only real use for access is for a single user db that you know wont get very big. If it's something that grows, then code a proper web based app and use mysql, or other db system.

I beg to differ. Access can be used to do a LOT of things it wasn't 'designed' for. Since you have access to VBA you can hook into ActiveX controls which allows Access to do all sorts of crazy stuff. We had a real time trading system based off of 3 Access databases as a prototype before it was written developed in C# and it worked just fine. Obviously there were lots of limitations, but for a proof of concepts it works great for rapid development.

 

[JasonCoder]

The de-facto gui tool for mysql has to be navicat. It doesn't do visual table design and mysql doesn't have diagrams as a first class concept like mssql so I can't help you there. Probably could use access, visio or something like erwin if the connectors are there. mysql is very command line oriented. Sun has guis for mysql but they are primitive IMO.

Probably one of the most confusing things to mysql newbs are the database storage engines. It seems crazy that there are options like myisam that don't support referential integrity but believe it or not it may sometime come in handy. In certain edge cases myisam tables can scream if you're willing to take the bad with the good. Personally I'd rather have the database manage the referential integrity for me.

 

[Saint Nick]

I thought that ERWin would generate MySQL code based on your design...

I guess ERWin isn't cheap, either :/

 

[SunnyD]

MySQL Workbench

MySQL Workbench is a next-generation visual database design application that can be used to efficiently design, manage and document database schemata.

It is available as both, open source and commercial editions. The Community (OSS) Edition is available from this page under the free software/opensource GNU General Public License (commonly known as the "GPL")

Please note that at this point only the Windows release and a Beta release for Linux and MacOS X are available.

The Windows version requires the .Net 2.0 framework which is integrated with Windows Vista. Windows XP SP2 users need to download the framework from here and install it prior to MySQL Workbench.

More information about the project can be found on the product page (here) and the MySQL Workbench Developer Central site http://dev.mysql.com/workbench/.

 

[imported_smile]

Try dreamweaver developer toolbox to generate the code for you, and view some tutorials like: Dreamweaver CS3 Dynamic Development
http://www.lynda.com/home/DisplayCourse.aspx?lpk2=424

To manage the database use: EMS SQL Manager for MySQL