primary and foreign keys

I am creating a database for a project, it currently has 4 tables. I have unique primary keys in each table, but did not use any foreign keys. Do I need to have foreign keys in my tables?

only if you want the database to enforce relational integrity, e.g. row cannot be added to child table unless parent row exists, parent row cannot be deleted while child rows exist, etc.

Is it considered bad database design not to use them?

no, i don't see why it would be

it is not considered bad form not to use something that you don't need

(yikes, three negatives in one sentence)

jp1234: Yes, Rudy is right; foreign keys are only needed if you need foreign keys (how's that for a tautology?). But, I must say it's impossible to give you good advice on a subject like this without knowing a thing about your database or your needs other than '4 tables with primary keys'. My point is, depending on what you are trying to accomplish, it might very well be bad form not to use foreign keys.

Granted, a 4-table database might be simple enough that no relationships between tables are needed. But, after studying basic principles of database design, you might find that you are opening yourself up to real troubles in the future, and that indeed your database should be normalized into several more tables in order to have true data integrity.

Pal's Linux RDBMS Library might be a good place to start reading about this some more. Look for articles and tutorials on "normalization". (Don't worry if you don't use Linux-- the principles are the same). Also, you might want to get a copy of "Database Design for Mere Mortals".

It is for a project at school, we are using an oracle database. We are creating a general help desk for an is/it department. Here is what I came up with for a layout of the tables. Primary Keys are noted by the *.

User Table
------------
*user_email
user_id

ADMIN TABLE
--------------------
*USERNAME


FAQ Table
----------
FAQ_subject
FAQ_question
FAQ_answer
*FAQ_number

Online reports TABLE
---------------
*OL_CASENUM
OL_NAME
OL_USERNAME
OL_PASSWORD
0L_EMAIL
OL_STATE
OL_TELEPHONE
OL_COURSE
OL_COMPUTER (PC OR MAC)
OL_OS (OPERATING SYSTEM)
OL_BROWSER
OL_ISP
OL_DESCRIPTION
OL_ERROR
OL_STATUS
OL_RESOLUTION
OL_SOLVEDBY
OL_DATE

ONCAMPUS REPORTS TABLE
----------------------------------------
*OC_CASENUM
OC_NAME
OC_DEPARTMENT
OC_EMAIL
OC_ROOM
OC_PHONE
OC_PROB_TYPE
OC_DESCRIPTION
OC_ERROR
OC_STATUS
OC_RESOLUTION
OC_SOLVEDBY
OC_DATE

Anyone have any comments/suggestions based on the table I posted?

Well, the obvious question is: where do we see data that is related?

For example, I see a User table, and then in the Online Reports table, I see a reference to OL_Username. Will people in the OL table people also exist in the User table? If so, it is a good idea to name these columns the same, and to enforce that relationship with a foreign key constraint. (Naming related columns the same makes for easy use of the NATURAL JOIN)

Also, I notice that your User table has user_email as the primary key. If this is the case, then what is the point of having a user_id. (generally, you need 'id' columns when the other data in the table is not enough to guarantee uniqueness, or if the 'id' references some other table or database which generates these IDs)

Generally, wherever you have a column that refers to something that can exist in another table, you have a good candidate for a foreign key constraint of some sort.

I also notice that your ONCAMPUS Reports table has a place for name, dept. etc... If we are talking again about actual people, wouldn't it make sense to have one centralized table with information about People, and then the other tables simply reference that table with a foreign key?

And, I see that in every table except ADMIN and FAQ, you have an Email column. Maybe that should be your foreign key to the User table.

Honestly though, it's still hard to know exactly what you need here. I really don't see a cohesive database model here. For example, I see Online Reports, and Oncampus Reports, and I have no idea what the relationship between those is supposed to be. In Online Reports, you have all kinds of information that has nothing to do directly with a report, such as Name, Password, Email, etc... There should be a generalized table with user information, and thus the report table doesn't need to be littered with repeated data. It should just be about the report with a single foreign key referenced to the user data. Basic rule of thumb: avoid repetition.

As I said, study some material relating to basic relational database design, as in the links I gave you, looking especially in the Fundamentals - Database Design area. (I know it's called the Pal's Linux library, but the concepts are the same whether you are using Linux or not). Maybe check out www.datamodel.org, or see some graphical examples of database models at www.databaseanswers.com/data_models/ . Also, for an excellent book on getting the basics quickly, look up "Database Design for Mere Mortals" at your favorite online bookstore.