Hi, Relational databases (and the language used to query them - SQL) have been around for decades, and have been used to run hugely complex applications. There are specific rules, and general guidelines that you need to follow in designing a database, so that it's actually queryable using SQL. Now, there is a tool called "Entity Relationship (ER)" modelling. Here you have Entities (a bit like "classes" in the OO world), relationships (between the entities), and attributes (a bit like properties). Normally, your entities will become your DB tables, and your attributes will become your fields. Relationships are constraints between your tables. There are other modelling tools as well. If you're having problems with database design (especially if you can model something in multiple ways in an ER model), then a DFD (Data Flow Diagram) is useful. This allows you to see the various actors in your system, and the actions that they would perform, and the data needed to perform the action. That gives you an idea of the queries that are going to be needed in the system, and you can pick the most appropriate schema to match. This is the book that I usually recommend to people: http://www.amazon.com/exec/obidos/tg/detail/-/0201708574 Database Systems: A practical approach to design, implementation and management. Besides some indepth discussion of transactions and so forth, it also covers ER, and EER (Enhanced Entity-Relationship Modelling), normalisation and so forth. You would probably need a good Systems Analysis book to cover DFDs in depth. However, it appears that you are in a bit more of a hurry, so some of the web based links that others have posted may be of more use to you. That said - if you want to build any sort of workable system that's going to be maintainable into the future, you *must* understand these modelling techniques. Otherwise you start to run into the issues that you have "the table is becoming unmaintainable". The techniques for building databases are well known - and no database should be difficult to maintain and query. You just need to know how to design/model it. I can't stress this enough - you simply can not build a moderately complex database backed system without knowing some theory. Spending 1-2 days grabbing as much info as you can on the topics will be a very worthwhile investment. It'll save you heaps of time down the track. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ : Thanks for the tip. So basically from all the advice that I've : gotten so far what is recommended is putting product specific : details in separate tables. Could anyone show me what a query : for displaying all products in the hard-drives category would look like : in a case like this? Learning by example is the best way? A : rough draft of a query would do. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ...and what are you going to do when you have 500 classes of product? Have 500 tables?!? That sounds a little crazy to me. But maybe you're not intending to have 500 classes of product? We don't know, because you don't know (or you won't tell us!) Also, sticking with the topic of "hard drives" - that seems fine for a stock standard internal IDE drive. You could extend it relatively easily to various sizes (3.5", 2.5", 5.4"). What happens when you have SCSI drives? They have a bunch of extra properties not applicable to IDE drives: Ultra320 -vs- Ultra160? LVD -vs- SCA? 68pin -vs 80 pin? And where do you put the external drives? (that have USB -vs- Firewire connectors), and NAS devices? and PC-Card hard disks? and CF Card hard disks (like the IBM microdrives). One possibility is the table layout that I specified before where the properties and their values are stored as rows, and you use some kind of roll-up query to get these into columns. But what you choose really depends on what your system needs to do! Apologies for making this so hard for you, but you're really putting the cart before the horse. You need to know what the system has to be able to do before you can design the table. To give an analogy (albeit a bad one), you're asking us whether you should put the website navigation on the left, or the top of the page, without giving us the slightest idea what the website's about, or how complex the site is, how big it is, and how "deep" it is (it is very deep/nested, or very shallow). Cheers Ken Microsoft MVP - Windows Server (IIS) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ From: "Cosmin G" <cosming at as.ro> Subject: [thelist] Re: Database schema : Well, too many answers, but none too clear. Maybe I should have specified : that I'm using MySQL. Which doesn't support many of the features that you : mentioned such as foreign keys, one-to-many relationships (searching the : manual for one-to-many found 0 results). : >Seriously, you need to use the tools that are out there that have been : >developed to solve exactly these types of problems ("what database schema : should I use?") : Which ones would those be? : When telling someone something like : > : > However, as stated before, you're jumping the gun. You need to do some ER : > model *first*. You need to create some DFD (or similar) *first*. Once you : > have these things, the database schema will write itself. : > : : You're not being helpful, especially to someone who's not very proficient. : What's an ER and what should it contain? : And what's wrong with posting my .sql file? It's not like I've posted a : virus, right? : : >I think it would make sense to break up the products table a bit. Any : >time when you find yourself with a table where you would have a large : >amount of fields that are not getting used, it's a good candidate for : >breaking into more specificized tables that get joined to the original. : >This is a clue that the table is decribing multiple entity types, which : >is usually suboptimal. I would suggest putting the printer and scanner : >specific fields in a common table though to accomidate : >printer/scaner/fax devices. : : Thanks for the tip. So basically from all the advice that I've gotten so far : what is recommended is putting product specific details in separate tables. : Could anyone show me what a query for displaying all products in the : hard-drives category would look like : in a case like this? Learning by example is the best way? A rough draft of a : query would do.