Database Design : Compound key vs surrogate key


I've got two questions.

The first, which is better a compound key or surrogate key I was under the impression that a compound key compossed of a user entered string and foreign key id will be slower and can cause more problems when compared to a surrogate key. Am I mistaken

Secondly, there was mention in another thread that it would be wise to use the abreviated value of states in an address table as opposed to placing the states in a look up table and joining the two tables. I understand that having to join two tables will result in slower query times but won't the size of the database be larger when compared to having two seperate tables and doesn't that break a rule of normalization
Posted On: Thursday 25th of October 2012 11:09:53 PM Total Views:  377
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Need help with creating a portable application with database.

I'm planning a portable logbook for employees to replace the current unstable Filemaker Pro one but I've got a few problems from the start. I don't know which database system to use. The project has to meet specific criteria: It is a logbook that stores information in a database and which can be used to produce reports detailing each employee's activity. It must also be: 1. Portable on a flash disk. 2. Easy to set up. Unzipping into a folder and being ready to go would be ideal. 3. Possible to create a backup tool that saves backups of the database from within the application. The frequency of the backups should be controllable and restoring should be easy. 4. Be able to import and export .csv files for integration/migration to and from the existing application. I do JavaScript, PHP, mySQL and general relational database design. I did know some ASP and VB some years ago. (I'm keen on CSS too, but that's icing). Options I've considered: a. MS Access. This would be easy to use since any machine with Access could be used and I could create the database, forms and reporting tools in one package. Cons are it is unstable, dependant on Access version on the machine in question and I don't know how to program it at the moment (I can learn). Even creating linked combo-boxes is hard compared to how easy it is in JavaScript/HTML. b. Portable Apache + mySQL and PHP/Javascript pages. This looks like it has a tough learning curve for me and it doesn't look like it will be easy for the users to setup and use. c. XAMPP and PHP/Javascript pages. You still have the setup difficulties for non-techy users and you have to start and stop services. This will be too much of a headache for the users - they really need something which is double-click and go. d. Online. Users would need net access, which they don't always have. This also means that the host (me) would have responsibility for the data held on my server. The users having responsibility for their own data is far preferable. I'm stumped at the moment. Access seems the easiest option atm but the instability of it is a big problem. Even small projects I've done recently have been prone to crashes (using Access 2007). Has anyone any ideas
VIEWS ON THIS POST

158

Posted on:

Thursday 25th October 2012
View Replies!

Could not select database - MySQL server has gone away

Hi Guys, Firstly I appreciate anyone reading this trying to help me because its driving me crazy! I have a script that imports a lot of csv files, these run fine and then all of a sudden stop! It stops at the same place everytime, but there is no code error as its all the same for every csv file. It does stop on a large file (45MB) but this may be just a coinsidence because i have set all the php limits to way above this. I have setup some security recently and wondered if it is this which is blocking me out of the mysql server after so many requests etc. Any ideas what could be causing this Many
VIEWS ON THIS POST

146

Posted on:

Thursday 25th October 2012
View Replies!

Sync 2 databases

Hi I have looked all over this forum but I am unable to find a solution to my problem. I have 2 DB's (MySQL on the web and MSSQL in the office) and I am trying to sync only the rows that have been updated recently from MSSQL to MySQL. I have a TIMESTAMP field on both DB's. What are the good ways to do this Are there any programs for this task Is there any way of doing this\t Please Help!! Many
VIEWS ON THIS POST

222

Posted on:

Thursday 25th October 2012
View Replies!

Help on database design

greetings I have to make a DB for a school but im stuck, the main feature its show student's grades but i don't know how to design it i guess there has to be an "students" table and a "courses" table but i don't know where the "grades" go should "students" table have (course1, grade1, course2, grade2...) fields or should i make a extra table named "class" with (course, student, grade) fields.
VIEWS ON THIS POST

127

Posted on:

Thursday 25th October 2012
View Replies!

deploy new changes to existing database

, Several days ago, I needed to upgrade one application by deploying some changes to one SQL server database. I tried to use ApexSQL diff tool but I got many error messages due to the dependencies (PKs and FKs). What's the best way to generate SQL scripts that upgrades my old database schema to match the new schema without data loss.
VIEWS ON THIS POST

352

Posted on:

Thursday 25th October 2012
View Replies!

Italian Characters are stored as "?" in the database.

I have a strange problem. My application supports Internationalization for Italian, French and German languages. The database is - MySQL 5.0.45 and the 'my.cnf' file has the following entries about the character set: Under - [mysqld] - Section init_connect='SET NAMES utf8' and under - [mysql] Section default-character-set=utf8 In the JSPs, the tag's content is set to "text/html charset=UTF-8". the tables are created in the InnoDB engine with defaul charset = UTF-8 and ENGINE = Innodb Application server: Sun ONE App server 9.1 Operating System: Redhat Linux Server version 4 Update 6. Italian characters ( & ) are stored as in the database. They are properly rendered on the browser and in the application log on the app server they are displayed as - & respectively. Note: When I deploy the application on Windows XP box, the characters are stored & displayed properly. Whereas, it behaves strangely on RHL box. Please help me. Thank you in advance.
VIEWS ON THIS POST

137

Posted on:

Thursday 25th October 2012
View Replies!

Easiest way to import excel file to database

Is there an easy way to import an excel file into a database making sure the cells in excel go to the correct rows in the mysql database
VIEWS ON THIS POST

214

Posted on:

Thursday 25th October 2012
View Replies!

Fastest way to search a large database table

I have a table of about 10 million rows and need to search 4 of the columns. Right now I am using mysql's built in fulltext searching capabilities, but as far as speed, it isn't cutting it. I'm getting anywhere from 10-50 seconds for a single search depending on the length of the query. Plus, since the results are paginated, the query needs to be run twice. I was just thinking there must be a method that is faster than this. Could anyone give me some input Any help is appreciated. Here is an example of what I'm using now. Code: SELECT SQL_CALC_FOUND_ROWS links. * , zip_codes.latitude, zip_codes.longitude, MATCH ( name, address, description, url ) AGAINST ( 'deli' ) AS rel, ROUND( ( ( ACOS( SIN( 34.169953 * PI( ) /180 ) * SIN( zip_codes.latitude * PI( ) /180 ) + COS( 34.169953 * PI( ) /180 ) * COS( zip_codes.latitude * PI( ) /180 ) * COS( ( - 77.87113 - zip_codes.longitude ) * PI( ) /180 ) ) *180 / PI( ) ) *60 * 1.1515 ), 1 ) AS dist FROM links INNER JOIN zip_codes ON links.zip = zip_codes.zip WHERE links.zip IN ( "28409", "28409", "28412", "28403", "28480", "28405", "28401", "284HH", "28411", "28428" ) AND MATCH ( name, address, description, url ) AGAINST ( 'this is a test search' )
VIEWS ON THIS POST

155

Posted on:

Thursday 25th October 2012
View Replies!

Last post within a database for a forum

Hi all, I am trying to create my own forum, but I am unsure how I get the last post for that part of the forum. I have the following SQL code Code MySQL: SELECT forums.id AS forumId, forums.forumName, areas.id AS areaId, areas.areaName, areas.areaSummary, COUNT(posts.id ) AS postTotal, posts.post FROM areas INNER JOIN forums ON forums.id = areas.forumId LEFT OUTER JOIN threads ON areas.id = threads.areaId AND threads.threadStatus = 'Activated' LEFT OUTER JOIN posts ON posts.threadId = threads.Id AND posts.postStatus = 'Activated' GROUP BY forumId, forums.forumName, areaId, areas.areaName, areas.areaSummary; Which gets me....... Code MySQL: +---------+-----------------------+--------+-----------------+--------------+-----------+-------+ | forumId | forumName | areaId | areaName | areaSummary | postTotal | post | +---------+-----------------------+--------+-----------------+--------------+-----------+-------+ | 1 | General Discussions | 1 | Lounge 1 | NULL | 2 | post1 | | 2 | Suggestion Boards | 2 | Company 1 | NULL | 0 | NULL | | 2 | Suggestion Boards | 3 | Company 2 | NULL | 0 | NULL | | 2 | Suggestion Boards | 4 | Company 3 | NULL | 0 | NULL | | 2 | Suggestion Boards | 5 | Company 4 | NULL | 0 | NULL | | 2 | Suggestion Boards | 6 | Company 5 | NULL | 0 | NULL | | 2 | Suggestion Boards | 7 | Company 6 | NULL | 0 | NULL | | 3 | Expert Advice (Q & A) | 8 | Expert Advice | summary here | 0 | NULL | | 4 | Restricted Area | 9 | Restricted Area | NULL | 0 | NULL | +---------+-----------------------+--------+-----------------+--------------+-----------+-------+ This is great however the last post within General Discussions is post2 and not post1. So how do i find out what the last post is for each section
VIEWS ON THIS POST

179

Posted on:

Thursday 25th October 2012
View Replies!

Speeding up my SQL Query on a large database

try without the ORDER BY and don't forget, it has to look at all rows in the table, unless you have an index on "field" and/or "$field"
VIEWS ON THIS POST

142

Posted on:

Thursday 25th October 2012
View Replies!

Using Terminal to configure MySQL database on Mac OS

I am in the process of installing and configuring MySQL and PHP internally on my Macintosh so that I can go through the tutorials in the book "Build Your Own Database Driven website using PHP & MySQL." I am on the step where I have to change my root password. I'm pretty sure that I've done everything up to this point correctly. I just keep getting the same error message when I try to connect to my MySQL database using this command: mysql -u root mysql Upon pressing enter, I get the following error message: -bash: mysql: command not found Please excuse my igonrance if this is an obvious question, but I am pretty clueless about all of this stuff. I've never even used the application Terminal before, so I am a total rookie.
VIEWS ON THIS POST

157

Posted on:

Thursday 25th October 2012
View Replies!

Select rows from a database

fellow SitePointers, I am curious as to how I would do a query to select a row from my database table if the id exists in an array I might need to select one row, or 100 rows, depending on how many values are in the array.
VIEWS ON THIS POST

120

Posted on:

Thursday 25th October 2012
View Replies!

How to design database

hello , I want to build a database for menu driven. like file --new --- html --- php --open --- html --- php --save edit --cut --copy --paste and like that. So,please help me how to start with designing the database as it can have n no. of levels.
VIEWS ON THIS POST

123

Posted on:

Thursday 25th October 2012
View Replies!

Best way to populate a database by hand?

Basically, I'm moving years worth of usenet txt files (from my domain, all my content) into a database, populating Title, Author, and Content fields. What's the best way to move these txt files into a database I imagine I'll be doing it by hand because you can't automate the Title and Author generation and I don't mind spending a little time "fixing up" the content body so it's all consistent, or adding tags. Also I'll be adding to the database a few times a week so I don't need to automate 100's of txt file -> database transfers. So can anyone give me a suggestion Right now I'm just making a simple form insertion page, but was wondering if there was anything more robust than doing it the archaic way.
VIEWS ON THIS POST

121

Posted on:

Thursday 25th October 2012
View Replies!

Migrate MS SQL database to new server

I've got a small (6 tables,
VIEWS ON THIS POST

203

Posted on:

Thursday 25th October 2012
View Replies!

database design conondrum

I'm trying to design a database for a client, and I've hit a bit of a tough spot. The database will be recording results for surveys, of which there are 3 different surveys based on the sub-type of user that the user is. So I have a users table table users ( id int primary key auto_increment, name varchar(200), type char(1), and so on ); There are also (currently) 3 survey tables, one for each type of survey as they have very very different types of information on them. So my question is how would I go about storing these survey results in such a way as it is easy to search them up later. Hopefully without multiple queries Any insight at all would be really helpful.
VIEWS ON THIS POST

129

Posted on:

Thursday 25th October 2012
View Replies!

Need Help(database backup)

, May I ask on how will you back up a database via cron jobs in an hourly basis
VIEWS ON THIS POST

138

Posted on:

Thursday 25th October 2012
View Replies!

Separate database???

I have a site on which i am using two tables> one for say articles and another for stories. There is no link between the two tables AT ALL and they are independent of each other. Also the tuples (rows) in both story and articles table varies. Now my question is should i keep both stories and articles in the same database or should i make separate db for each of them. eg db_stories and db_articles having stories and articles respectively\t Does it make any difference if i use different db for each Also once the above thing is finalized I would like to have a common search; which will extract data from the articles and stories both stored in the db for a input by user. Is it possible\t If yes please explain/elaborate and give some examples to how to implement a common search Please give your comments/suggestions keeping the site security, easiness to work in mind andn any other parameter which is important
VIEWS ON THIS POST

132

Posted on:

Thursday 25th October 2012
View Replies!

Having trouble importing databases. vbulletin

The sql file is too big for phpmyadmin, and they suggest ssh, but im not so good with it, and i have tried every combination of command I have found when researching on google. I then came across a script called "bigdump". I have all tyhe settings correct in the bigdump.php but I keep getting this error: Database connection failed due to Access denied for user 'USERNAME'@'cgihost' (using password: YES) they ask to change these settings which i have the correct DB username and pass. The server isnt "localhost" with my new hosting company. (netfirms) $db_server = 'MYSQLHOST'; $db_name = 'NAME'; $db_username = 'NAME'; $db_password = 'XXXXX'; I have tried changing permissions and following all directions from bigdump. could someone please help me
VIEWS ON THIS POST

163

Posted on:

Thursday 25th October 2012
View Replies!

which database to implement for my site ?

hello I'm in the proces of creating a new website that in potention could become huge and could attrack many tousands of visitors who will register themself. The database will become very simple, only fields: Name, Email address, Password, country and some similar fields containing numbers only which all should be inserted through the website. I like to hear opinions about which database i should use for this website because my knowledge about databases is very slim. 1. Access -> I have experience with using Access with websites so i could build this myself but i heared this database type is more suitable for smaller number of records etc. Also i have experience that Access regular give errors because multiple users are accessing the Access database at the same time And how about speed with an access DB has for example 500.000 records 2. SQL -> What i understand more suitable for larger databases, no problems with users accessing the database at the same time Problem with implementing SQL is that i have no knowledge how to build this If SQL is the most suitable option i will consider to hire someone to build it for me. How is the speed with SQL databse when reaching lots of records 3. Other databases Hope to hear some suggestion from you ! Kind regards Database Newbie
VIEWS ON THIS POST

136

Posted on:

Thursday 25th October 2012
View Replies!