Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(First time poster, very very long time lurker. I cannot find an answer to this question anywhere, so I now come out from the shadows to ask the experts).

Is a junction table with say 4 foreign keys (4 tables), a good idea for tables that are only to be Read as a Lookup and not changed regularly, and which contain static data and would only be changed by an admin and not a 'user'?

I'll give a simple example.

Say we have four tables each has only two fields - an ID number field (Primary Key) and a 'Name' (text) field: tblCarModelName, tblCarType, tblManufacturer, tblColor.

And then we have a junction table (tblCars) which is designed as follows:

Car_ID (PK)
CarModelName_IDFK (FK)
CarType_IDFK (FK)
Manufacturer_IDFK (FK)
Color_IDFK (FK)

The table could look like (please note the IDFKs will be integers not strings as illustrated here):

Car_ID, CarModelName_IDFK, CarType_IDFK, Manufacturer_IDFK, Color_IDFK
1,Hilux,Truck,Toyota,White
2,SLK300,Sedan,Mercedes,Blue
3,SLK500,Sedan,Mercedes,Silver
4,Prius,Coupe,Toyota,White
....

So the table ends up looking like a spreadsheet. But why would I want to do this?

  1. Makes data entry and table maintenance easy.
  2. I can still query the table for a particular say 'Manufacturer' because of the foreign key reference.
  3. I can paste data structured in the same way from Excel directly into the table.
  4. And also, more importantly, many-to-many relationships are handled in this example.
  5. If I offer this table the exact same way in a user interface form, I know when the user chooses from this list, say ID = 3 and that they have chosen "SLK500,Sedan,Mercedes,Silver" from the list.

Is this design ideal? Feedback is appreciated.

What is this model called anyway?

share|improve this question
    
If it helps what you are doing is just a simple cross reference table for a many to many join. You are just doing it with 4 tables instead of 2. –  Kenneth Fisher Oct 24 '13 at 13:13
    
@KennethFisher Thanks. I was concerned because I've looked for examples on the net and haven't seen this type of table used to join many-to-many relationships with 2+ tables. I've only ever seen junction tables used to join 2, not 4 tables. Is this type of cross reference table common? –  zdxai Oct 24 '13 at 22:19
    
It's not terribly common because you don't have frequent uses for it. But yes I have seen it done before on several occasions and it does work just fine. –  Kenneth Fisher Oct 25 '13 at 1:25
add comment

3 Answers

up vote 1 down vote accepted

There is nothing wrong (in principle) with a three way (or more) intersection table, as long as it properly describes your relation.

However: Your particular table is not in third normal form (3NF).

You should generally normalize to 3NF unless you have good reason not to.

The problem is that some of your data depends on part of the key, instead of on the whole key. This means your table is only in second normal form (2NF).

Consider this: Your table would allow a record that says a Prius is a Toyota and another record that says a Prius is a Chrysler. Obviously that's not good.

You want to remove the hierarchy of make and model into a separate table.

Similarly, the type of a car is dependent on the model, but not on the colour.

So what you need to do is separate these columns too. What you really need are four tables:

  • Manufacturers (manufacturer name)

  • Models (model name, FK to Types and FK to Manufacturers)

  • Types (type name)

  • Cars (FK to Models, colour)

This would give you a normalized schema with no redundancy and very little risk of data corruption due to insert, update and delete anomalies.

share|improve this answer
    
I've seen that argument thrown around a lot by DBAs (prone to mismatching ie. "Prius,Toyota" vs "Prius,Chrysler"). However if you think about it - ALL tables are prone to mismatching due to user error. Even with the 'Models' table in your proposed solution - I or an Admin could still mistakenly mismatch a model/type/manufacturer. An admin could even mistakenly type 'icecream' into the Model name field (only god knows why they would..but just an example). Maybe im too much of a novice to understand the logic or concept behind the whole "mismatching argument" –  zdxai Oct 25 '13 at 2:52
    
@zdxai - Draw your tables defined the way I suggested, and show me a sample population where you can have one example of a Prius being a Toyota and another example of a Prius being a Chrysler. Normal Forms can't stop your database from being populated with data that is wrong, but it can stop your database from being populated with data that is inconsistent. If you don't see the benefit of the normal forms then you haven't studied them hard enough. They are the #1 best practice in database design. –  Joel Brown Oct 25 '13 at 3:38
    
Challenge accepted, i'm happy to run through your example and learn the hard way. Can I clarify though, are "model name","type name", and "manufacturer name" each PK's to their respective tables? –  zdxai Oct 25 '13 at 3:47
1  
@zdxai - As long as your tables have a PK, it won't matter for the exercise whether the PKs are natural or surrogate (i.e. a string or a sequential number). You can make the names the PKs of their respective tables if you like, or you can make the keys integers to avoid the issue that David Cummins pointed out in his original answer. –  Joel Brown Oct 25 '13 at 3:54
    
IF SO, then I can see now what you mean now. 3NF helps keep the data consistent (if Prius is a primary key of the Models table), but can't stop users from populating, say the FKs in the Models table, with the wrong data (so they could still choose the wrong Manufacturer FK for the Prius). Does that effectively summarize your main answer above? –  zdxai Oct 25 '13 at 3:55
show 4 more comments

Whether to normalise or denormalise data depends upon how you intend to use it. For normal usage patterns (e.g. searching, viewing records one page at a time) I would keep it normalised. There are uses for denormalised data, e.g. some forms of reporting, but that doesn't sound like what you're doing. I wouldn't structure my database based on the ease of pasting from Excel, that impacts your opportunities to make a more user-friendly system that also applies better input validation.

I don't recall the exact specifics of the different normal forms, but you can check them out here. I would suggest changing the hierarchy because a Model and a Manufacturer are traditionally tied together, e.g. nobody but Toyota makes the Hilux. So your Model should have a ManufacturerID and the Car doesn't need a ManufacturerID. What I said re validation is that with pasting from a spreadsheet you could make a typo and enter "Tyoota", whereas if you had an admin section that requires you to select a make/model from dropdowns or similar you couldn't make that mistake.

Edit: The below is out of date because the OP clarified that the sample was merely a sample, not the way it would be stored in the table.

The reason your table tblCars looks like a traditional spreadsheet is because you're not using the integer keys from your four other tables to join to them. If you were, every column would be an integer.

The bonuses of doing it this way include:

  • Integers are fixed width.
  • Integers are generally shorter than the strings that they represent.
  • Data entry errors in model names etc can be fixed in one place.
share|improve this answer
    
Thanks David. Actually those FK columns will indeed be Integers, I just wanted to illustrate with actual strings/names. But yes, those FKs will be Integers joined by the ID numbers of those tables. So your response indicates im somewhat on the right track? Is the table I have presented above normalized? I'm a bit confused. It doesn't seem to validate any input either, ie it allows multiple combinations of choices which could be incorrect combinations. But the Admin is going to manage this table, and so will ensure its validity before presented to the user in a form for choice –  zdxai Oct 23 '13 at 23:19
    
Right, I see. I will update my answer to reflect that. –  David Cummins Oct 23 '13 at 23:40
add comment

Most of the time, lookup tables describe subject matter entities while junction tables describe subject matter relationships. If you want a conceptual model of the data and one that is closely linked to the subject matter and the project requirments, I recommend the Entity-Relationship (ER) model. If you want a logical model that captures your design of the solution, I recommend the relational data model. The SQL model (lookup tables, junction tables) is close enough to the relational model for most purposes.

Neither of these models correspond to the way an application user sees the data, in forms and reports. The creation of forms and reports from tables is non trivial, and also tedious and error prone. You can get help from automation tools in a variety of application builders. Perhaps one of the simplest is MS Access, with its forms wizard and report wizard.

Over time, you will add to your very simple model. For example, you may want the website URL for the Manufacturer. You are going to want to store this in the Manucaturer lookup table, and not in your one great big table for a variety of reasons. By the time you get up to about a hundred tables, five hundred columns, and maybe a half a million rows, you'll understand why one great big table is a bad design. It's bad for perfomance, it's bad for data management, it's bad for clarity, and it's bad for application programming.

As long as you stay with tiny examples that don't even need a database, you can stay with one great big table.

share|improve this answer
    
I will research accordingly, thanks Walter. –  zdxai Oct 25 '13 at 4:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.