The following is a listing of general categories of tables that you find in databases. Emphasis is placed on the roles of each table type. Note also that this category listing is simply my own paradigm based on my own experiences. Other people will surely disagree with me (but I suspect their disagreements would be mostly on the details). I present this info to help you get a grasp on database design. Problem solving is easier when you can break down the problem, and this listing is one approach to breaking down database tables into different types.
Definition: This is a "main" table in your database. Without these, there really wouldn't be any reason for the database to exist.
How to Identify: You can identify the Core tables because they contain the most relationships to other tables. They will frequently have relationships to numerous Lookup and Detail tables. They also typically contain more fields than any other table in the database (but not always).
A Core table typically contains (at minimum) the following fields, using certain naming conventions.
Definition: A Lookup table is a "reference" table that contains categories, types, keywords, tags, or other "classification" labels. Typically these categories represent some kind of finite scale. Examples include: priority, severity, frequency, quality, etc.
How to Identify: A Lookup table typically has a single relationship to a Core table or Detail table and usually contains only 2 or 3 fields. Due to their nature, they are not sorted alphabetically.
Warning on Naming Lookup Tables: Having already described Lookup tables as containing types, categories, or tags, I strongly discourage you from using the words "class", "type", "category" or similar to name the table. Why, you ask? Simple answer: What does the word "type" mean? If I have two Lookup tables that both list "types" of a Core table record, how exactly do I distinguish between those two "types"? Here's a guideline to follow: if you are about to use the word "type" in a Lookup table name, ask yourself "What 'type' of thing is this?" The answer to that question is the name you should use instead.
A Lookup table typically contains the following fields, using the same naming conventions as Core tables.
When you add a new text field to a Core table, ask yourself: "Does this field represent a 'type' or 'class'?" If so, you should not add a text field, you should add a Lookup table instead.
In order to make your Lookup table work with your Core table, you need to be able to create a relationship between them. Thus, we need to add another field to the Core table: a foriegn key. This field should be a Long Integer to match the type of the primary key in the Lookup tables. They should have the same name as the primary key field name of the Lookup table.
Why do we create relationships based on numbers instead of text? Valid question. Here's why:
Definition: This is another kind of "reference" table, similar to a Lookup table, but it contains a longer listing of records and new records will frequently be added. (Hence "List".) Examples include: Vendors, Shippers, Suppliers, Reasons, Purposes, Genres, etc. You could think of it as a "Growable Lookup" table.
How to Identify: A List table typically has a single relationship to a Core table or Detail table but often contains numerous fields. These typically are be sorted alphabetically. List tables might even have Lookup and Detail tables related to them!
Warning on Naming: The same warning given earlier regarding naming Lookup tables applies to List tables as well.
More on Adding Core Table Fields: The same counsel given above on adding a "type" field to a Core table applies here: don't add a text field when you should be adding a List table instead. To make List tables work, you need to be able to create a relationship between Core and Lookup tables. Thus, we need to add another foreign key field to the Core table, just as we did for Lookup tables.
Note: Because List tables have the potential to grow into whole entities of their own, some people prefer to implement them as Core tables from the start. It's only a naming difference after all, and a rose is a rose.
Definition: A Detail table contains specific items that are subordinate to the records in a Core table. Examples include: Order Details, Event Logs, Incident Reports, User Comments, and so forth.
How to Identify: A Detail table typically has a single relationship to a Core table, hence it contains a field that is a foreign key to a Core table. It also typically has a 2-part name, the first part being the same as the Core table to which it is related. For example, if there is a table called "Orders", it might have a Detail table called "OrderItems". It is not unheard of to have a Detail table with Lookup / List fields related to it. Sometimes Detail tables will contain further Detail tables, but this is rare.
Happily, you do not need to add any additional fields to your Core table to create a relationship to a Detail table. This is because the Detail records are responsible for maintaining the relationship not the Core table.
That having been said, I have some additional counsel for designing your Core tables: Add additional fields as needed to your Core tables but only if you can guarantee that a given record will never require more than one instance of that field. If you can imagine a case where more than one instance will be required, make a Detail table instead. You can tell you've made a mistake, if, for example, in a "Contacts" table you have fields that read "PhoneNumber", "PhoneNumber1", "PhoneNumber2", etc.
Definition: A Cross-Reference table exists only to create a many-to-many relationship between two other tables. Typically, these two other tables will be Core tables, but that is not a requirement.
How to Identify: You can identify Cross-Reference tables because they typically contain only 2 fields: 2 foreign keys that reference primary keys in Core tables. A compound primary key will be made out of these two foreign keys, so as to disallow duplicate entries of the same two foreign keys.
When you add a new foreign key to a Core table that will reference a Lookup / List table, ask yourself if there is a chance that two of the categories in that Lookup / List table could describe the Core table record, you should be adding a Cross-Reference table instead. For example: if I was making a "Movies" table, and I was about to add a foreign key to reference a "Genres" List table, I should ask myself: "Is it possible that a movie could fit into more than one genre?" If the answer is yes, I should add a Cross-Reference table instead.
Note: Cross-Reference tables are the least common of all of the above tables because they serve a very special purpose: to create a many-to-many relationship. RDBMSes that provide built-in support for many-to-many relationships will not need Cross-Reference tables. From another point of view, any table that contains two foreign keys can be used as a Cross-Reference table, so these could arguably be considered the most common of all of the above tables.