Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tarana
Starting Member
12 Posts |
 Posted - 2011-08-14 : 15:44:42
|
| I have a table with about 30 columns & the table has 20k rows.While querying, for query1 I use fist 15 columns & for query2 I use last 15 columns. But there are 2 columns ID & Name columns which are common to both queries.ID is nchar(10) & Name is a varchar(100).With these considerations, should I keep this as one big table or split into 2 tables with ID & name repeating in the 2 tables?What are the pros & cons of each? |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-08-14 : 15:51:18
|
| Hi Tarana,I don't think that the queries your running will have much bearing as to whether you should split the table. Is your database normalized? (ie, does the table contain duplicate values etc?) If it is properly normalized, then it shouldn't really matter that you're querying the table for two different purposes. If it is normalized and you're running into performance issues with the queries, you may consider setting up indexes (I hate that that is not "indices" in Database world!!!) to meet your querying needs.Can you post the DDL (ie create yourtable (ID nchar(10), name varchar(100)...columns n,...)along with some sample data and the purpose for your queries? Also, if you can show any possible duplications in data, that might be helpful in deciding whether or not you need to redesign your table(s). |
 |
|
|
tarana
Starting Member
12 Posts |
Posted - 2011-08-14 : 17:15:28
|
| ID is the primary key & the table will be sorted based on this as the query will be mostly based on this column. There is also another column which is subset of the ID - nchar(10) column but human readable - text form - nchar(2) and some queries will be based on this column.I this respect there is duplication yes, but data in other columns are are unique to the row.I still have not set the tables yet, so no DDL yet.The first 15 columns are customer contact & will be nchar/nvchar and 2nd 15 columns are product quantity & will be ints |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-08-14 : 17:30:14
|
| Hmmm, well without seeing the full DDL, I'd have to say you'd want to have 2 tables or more. I'm not sure what the relationship is between customer contact and product quantity, but if you want to be able to have a one to many relationship between the 2, you'd need to create two or more tables.I'm imagining your business model to be something like this:You have a customer list. Within the customer list, you may have multiple contacts for each customer. Each customer may be able to have one or more products/orders?If this is the case, you'd probably want tables like this:Customer (contains all your distinct customers)CustomerContact (contains all the various contacts for each customer...ie home, business, mobile)Items or Products (all your distinct products or items available)Orders (Each distinct order that can be generated for a customer which can contain various products/items on one order)There are tons of references to this type online as well as sample databases such as Pubs, Northwind and Adventureworks. They're all readily downloadable online and very helpful for practice/review and to help decide which way might work for your own business model. |
|
|
|
Celko
Starting Member
23 Posts |
Posted - 2011-08-14 : 21:45:59
|
| >> I have a table with about 30 columns & the table has 20k rows. <<Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include out Put. This is explained IN the FAQ section. We can not read minds; what would we need to know to do your job for you? Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL. This request is like sending a letter to a doctor and asking for medicine without giving any information at all. “Hello! My name is Fred! Send me drugs!”>> While querying, for query1 I use fist 15 columns & for query2 I use last 15 columns. <<Queries? What query? I don't see any queries. >> But there are 2 columns ID & Name columns which are common to both queries. id is NCHAR(10) and name is a VARCHAR(100). <<You never read a a book on data modeling have you? There are no such things as a magical, universal generic “id” or :name” in RDBMS. The first law of logic is called the law of identity – “To be is to be something in particular; to be nothing in particular or to be everything in general is to be nothing at all”What are you modeling in this table? I saw one table with 120+ columns, one for each lab test and one row per volunteer in a medical experiment. That was the right way to do it. While not absolute proof, most identifiers in a properly designed schema are drawn from the Latin-1 character set and are made up of alpha numeric with minimal punctuation. ISO, ANSI, DIN and the industry standards groups do this for portability. It is guaranteed to be a valid subset in ALL languages gy UNICODE. But your magical, generic, vague “id” can be Chinese characters! Likewise, magical, generic, vague “name” has a very bad code smell because very few names for people, places or things are 100 characters long.Author ofSQL for Smarties Thinking in SetsSQL Puzzles and AnswersAnalytics and OLAP in SQLSQL Programming Style Standards in SQLData & DatabasesTrees & Hierarchies in SQL |
|
|
|
tarana
Starting Member
12 Posts |
Posted - 2011-08-14 : 23:45:11
|
| Thanks flamblasterYou have answered my question. |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-08-15 : 00:26:36
|
| Sure thing...If you're interested, I would recommend a book by Michael Hotek called SQL Server 2008, a Step by Step guide. I've read a lot of SQL Server self help books but it's the only one that's really jived with me. Mr. Hotek really does a good job of explaining the fundamentals without losing his audience. It's very methodical and has helped me immensely.Good luck! |
|
|
|
|
|
|
|
|
One table with all Columns or Split to 2 tables