Page 1 of 1
Table Structure
Posted: Fri Jun 05, 2009 12:22 pm
by Chalks
I'm creating a system that allows you to sort items into categories. The tables look like this (basically):
user table
uID
category table
uID
cID
item table
cID
iID
my question is this: all of the items will be in a category (always). Should I also include the user ID in the item table, or is it not needed since the category IDs will always be unique?
Re: Table Structure
Posted: Fri Jun 05, 2009 12:41 pm
by mikemike
Depends how big your table is. I'd say not needed.
You could do that with just one table, you know? Using category_id and parent_id, assuming it's like a tree view that you're creating? That way you can have unlimited sub-levels.
I may have the wrong idea of what you're trying to accomplish though
Re: Table Structure
Posted: Fri Jun 05, 2009 1:23 pm
by Chalks
I want the user to be able to move things around and delete things (even categories) though. I'm not really sure how I could accomplish that with just one table.
as for table size, I imagine about 3 categories per user and ~30 items. Depending on the number of users, that could add up.
Re: Table Structure
Posted: Fri Jun 05, 2009 7:08 pm
by califdon
I get a little rabid on this subject, so if you don't want to hear my rant and rave, you can quit reading now. Relational database design is NOT a matter of "this is the way I like to do it" or "here's one good way you could do it" or any other hand-waving, "mushy" operation. There is a clearly defined process that was developed almost 50 years ago and the entire SQL language is based on data being stored in accordance with the rules that were developed by Dr. E. F. Codd at IBM. The only problem is that it is now so easy for anyone to install a good relational database engine and start designing databases without ever studying the well-known (and actually rather simple) principles that Dr. Codd specified, that rather few people, other than academics, have bothered to learn them.
Everything is based on clear definitions of what "entities" you will represent in your database. An entity is like a noun in grammar, a person or an object or an event, etc. Typical entities are Customers, Parts, Trials, Enrollments, etc. If you can clearly define your entities, and then define the "attributes" of each entity, such as name, ID No., date of birth, color, size -- then you will be able to quickly determine what should be in each table and how to relate them to each other. If you haven't defined the entities (in writing is almost mandatory, except in the simplest of cases), you will wallow around in confusion and very likely never have a database schema that really meets your needs.
So my recommendation to you is to WRITE DOWN what each entity is. Sure, "user" is a probably a person, so that's surely one table, and each row must be identified with a primary key, and will have all sorts of attributes, maybe password and email address, etc. That was easy. Now what is an "item"? What does it have to do with a user? What is a "category"? And so on. If you can't write down a clear definition, it usually means you haven't identified a real entity. That's the purpose of this sort of process, to clarify your thinking about what the data means that you're going to store. Every entity will have its own table. Every entity must have a unique identifier (primary key). You will need some foreign keys. You may need linking tables that define many-to-many relationships, containing perhaps just a pair of foreign keys, or sometimes other data that pertains to the relationship, not to an individual entity. When you have done this analysis, then you examine your complete schema and start applying the rules of data normalization, to reduce redundancy and eliminate partial dependencies. There are loads of tutorials online about normalization.
You never need to duplicate data in different tables, other than foreign keys, and generally you will just create opportunities for your data to become inconsistent if one record is changed, but not others. SQL is designed to join data from different tables and make any logical connection that can be made, if the tables and their fields are structured according to these rules. The number of records plays no part in any of this. It works for 2 records and it works for 2 billion records, exactly the same.