Database design tips

bsshetty17 has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Database design tips by tobyink (Canon) on Feb 04, 2014 at 11:13 UTC
Read up on database normalization. Database normalization is important because it ensures that you're not storing the same data in multiple places. Storing the same data in multiple places complicates updates and deletes, and means that you're running the risk of your data becoming inconsistent - where one table is telling you something, but another table contradicts it. There are various targets in database normalization called "normal forms". Research them. The first three are quite easy to understand. It's generally a good idea to make sure your database conforms to 1NF, 2NF, and 3NF, and also Boyce-Codd normal form, which is a little harder to understand. Checking conformance to the higher NFs generally has limited benefits, and except in unusual circumstances, most 3NF databases tend to satisfy 4NF and 5NF anyway. `use Moops; class Cow :rw { has name => (default => 'Ermintrude') }; say Cow->new->name`	[reply]
Re: Database design tips by karlgoethebier (Abbot) on Feb 04, 2014 at 11:42 UTC
Some examples might be helpful. Please see: MySQL Sakila Oracle Database Sample Schemas PostgreSQL Sample Schemas Regards, Karl ŤThe Crux of the Biscuit is the Apostropheť	[reply]
Re: Database design tips by pajout (Curate) on Feb 04, 2014 at 12:47 UTC
Please, feel it as recommends, not as commands: - Do precise analysis of the project. Devil is in details. - Adopt some visualisation of db schemas and use it strictly - I recommend crow's foot notation on physical data model. http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model - The best user friendly developing tool is pencil and paper. - Designing tables, be very sure of cardinality/relationship and do not implement too generalized schemas (if the project requires just 1 to many relation between tables A and B, implement just this) - Do not implement application logic onto database schema and/or stored procedures. This layer should just keep data consistent, regardless on the logic.	[reply]
Re: Database design tips by vinoth.ree (Monsignor) on Feb 04, 2014 at 10:39 UTC
Hi PublicAccess A Simple Guide to Database Design in MySQL Just Google it You will get lot of info. *All is well*	[reply]
Re: Database design tips by Anonymous Monk on Feb 04, 2014 at 09:51 UTC
copy what others have done :D	[reply]
Re: Database design tips by stefbv (Priest) on Feb 04, 2014 at 13:15 UTC
And while learning, you will need to make many changes to the database schema, use Sqitch for change management.	[reply]
Re: Database design tips by Anonymous Monk on Feb 04, 2014 at 18:01 UTC
Quite possibly the best thing to do is to look for someone in your company who DOES have the knowledge that you lack. The design of a database is fundamental to every aspect of any application which uses it, and is very difficult to change because of its impacts upon then-existing code. If you screw-up now, you'll feel screwed-up for good. ;) (And you might really BE "screwed.")	[reply]