I'm starting my first real opensource project, and it involves management of a print shop's functions and will rely heavily on database transactions. Because of PostgreSQL's reliability and licensing, I have decided to use it for the foundation of my app (with appropriate PEAR abstractions since I'm starting a PHP web based app for portability and because of my experience using this language). This question might not be that PostgreSQL specific, but I decided to use this app and maybe there are specific things it does that may help here (problably not, but hey)
I really hate the software we use at work and have, over the course of the past 6 months, put together a fairly extensive list of annoyances I do not want my database to have. Scalability and flexibility have been prominent in my mind.
Anyway, to get to the jist of my question for you all, I envision my layout consiting of (for each entity type) a table for the fields necessary for that entity, and the table with each field's info listed as a row/record. Many to Many relationship.
Field Number (PK)......Field Name
Company Number (PK)......Field Number......Info
1.....1......International Business Machines
1.....3......1000 Big Money Circle, NY, NY
1.....3......1253 Santa Cruz Whatever, CA
I think you get the idea. Obviously this many-to-many will cost more processing power than simply a 1NF listing of company names and addressess, but in addition to companies like IBM where they are commonly abbreviated, we have customers who operate companies under different DBA's for different things, or work from their home sometimes and their business sometimes, and I'd like to keep all their pieces propely labeled for accounting purposes yet grouped. It's also important to normalize the industry they're in since most companies do more than one thing (depending on how important the boss thinks having such details are, but I'm coding FOSS and I want to have every possible avenue of information available for people like me .
I'd like the project (and I plan on coding it that way) so that it could, in theory, scale onto a national level with branch hierachies, but in reality, our shop might run a few hundred or thousand queries a day.