• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Is mySQL supposed to be this confusing?

fuzzybabybunny

fuzzybabybunny

Moderator<br>Digital & Video Cameras
Moderator
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?
 
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.
 
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.
 
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.
Click to expand...

So for small websites should I just stick with Access because it's easier and faster to learn?
 
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.
Click to expand...

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?
Click to expand...

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.
 
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.
 
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.
 
1. No nice GUI like Access. Can't visually see and make relationships to tables. I'm using phpmyadmin.
Click to expand...

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...
Click to expand...

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?
Click to expand...

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.
Click to expand...

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?
Click to expand...

No, at best Access should be used for prototyping things. I can't imagine it being a good idea for anything in production.
 
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.
Click to expand...

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
 
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.

 
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.
 
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.
Click to expand...

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.
 
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.
 
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/.
Click to expand...
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top