2

Possible Duplicate:
Is it necessary to create a database with as few tables as possible

I've recently started to develop a database for a ~10 person office (it's an internship for me), and I've come up with a conceptual design I am quite happy with. My employers though, wish for me to simplify my design (less tables) because they wish to extend onto the database when I leave them and go back to school.

Two of them have some MSAccess experience, and they seem to know the term "database normalization", but none of them have a CS background. To accommodate this, I'm already going to build in MSAccess, which I do not have fond feelings for. I've explained that the conceptual ER diagram is not a table schema, and still needs to be translated into one and some of the relationships in the ERD might be one table with an entity, and simplifying the schema will lead to a "less normalized" database. The feeling I'm getting is that what they would like is for the database to just barely make it as 2NF, and would love it if it were 1NF.

Should I explain that views can be defined on the schema to make querying simpler? But I do not know how I can explain to them how to extend onto the design once I am gone without trying to teach them database theory, which they don't have time for. Or I could just make it less normalized - they're the ones paying for it and using it? I don't know.

Improve this question
0

2 Answers 2

Reset to default
1

Build the client not only what they need, but in most cases you need to give them what they ask for as well; in this case, low normalization gives them both. Not to mention, interns don't often have much room to argue.

So because of all this and the fact that you indicated, "non-expert users" need to maintain this product when you are long gone and this project is out of your hair...you should keep things simple.

Finally, normalization is not always preferable. There are numerous articles debating the pro's and con's.

Improve this answer
3
  • thanks for the advice! Yes, I will be leaving this project in 4 months, so I guess it is best for me to keep it as simple as possible. What about storing two separate entities in one table just because they have the same number of fields though? I don't know if that is a normalization issue, it just seems dangerous...
    – Saaraneth
    Oct 11, 2011 at 20:16
  • @Saaraneth: Would you be giving the same column multiple purposes? That's something generally to be avoided. It seems "simpler" at first (fewer tables), but it is most likely going to cause more confusion in trouble (than two separate tables) as more time passes (unless of course the only person using this system is extremely diciplined... OR if the n-entities-1-table thing is maintained by well-tested code, never touched by human hands).
    – FrustratedWithFormsDesigner
    Oct 11, 2011 at 20:39
  • From a DB perspective that is certainly not an ideal situation. If for nothing else, there's double the potential to change. But, the fact is that some big systems do kludgy little hacks like that all the time (ehem SAP..). The question you need to ask is how much is a change going to cost? There may be a lot of infrastructure in place already built to support the single table design. You need to be able to justify the cost. If the cost is very low (none of that stuff I mentioned) then tell them they should split the table into 2 (or just do it).
    – P.Brian.Mackey
    Oct 11, 2011 at 20:40
0

It is good that you know the concepts of Normalization and schema design. It is important to determine what is the system about? This affects your schema design greatly.

For example if the application is mostly about searching for information and reporting, then there is a type of schema called star schema which can be used. This is not a normalized data view but it helps reduce query complexity.

You can still have good schema by removing unnecessary look-up tables and place the data in files to be loaded by the application. This may cut your table by about 30%.

Your suggestion to create views over the existing schema is a great one also.

If you can determine the queries that will be required you could easily create the necessary views and the reports before you leave.

The last thing you should attempt is to teach users about database theory. Not only because it is usually a long way, but also because the can make mistakes and produce wrong results.

Most users want nothing but push a button. Some consider this too much to do :)

Improve this answer
4
  • Yes! The application will have a lot of information reporting. Googling star-schema right now. Thanks. Would putting look-up tables in files increase run-time? I've noticed that MSAccess is already not fast.
    – Saaraneth
    Oct 11, 2011 at 20:19
  • 1
    @Saaraneth: Access is not a tool I would normally recommend for users that need critical performance. You can have Access analyze your tables (based on usage in queries) and suggest new indicies, that might help a bit. This may help: support.microsoft.com/kb/292799
    – FrustratedWithFormsDesigner
    Oct 11, 2011 at 20:41
  • Focus on indexing the database properly and limiting result sets to only the necessary fields.
    – JeffO
    Oct 11, 2011 at 21:18
  • Access has an overall acceptable performance for desktop applications with few users. It has been in business for several years. @JeffO recommendation is important. This link may help you tune the database: office.microsoft.com/en-us/access-help/…
    – NoChance
    Oct 12, 2011 at 0:13

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