| Author |
Topic |
|
NPR
Starting Member
15 Posts |
 Posted - 2004-03-22 : 13:41:05
|
| Hello,I have a table with 40 columns in it. Each column is a tiny int field which when displaying the information will need to be joined to a table. So I will have a query with about 40 joins in it when I need to display all the information.Is this a good way to go about having things setup? The table is a bit big but all the information in the table will be displayed at the same time. I will never have to pull just part of the info so it didn't make any since to me to break it up into other tables. That would just create extra join statements.Let me know what you guys think.Joey |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-22 : 13:46:06
|
| You have a table with a 40 column composite Primary Key?What's it called?Brett8-) |
 |
|
|
NPR
Starting Member
15 Posts |
Posted - 2004-03-22 : 13:48:48
|
| I am not sure I understand your question. Yes it is 40 columns. One column is the primary key which is setup as an integer. Then all the other columns are tinyints. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 13:53:34
|
| So are your 39 columns foreign keys on other tables?Tara |
|
|
|
NPR
Starting Member
15 Posts |
Posted - 2004-03-22 : 13:58:10
|
| Right the other 39 columns are foreign keys that match up to other tables. For instance in the main table one of the columns is Gender and then that matches up to a table that has the following info in it:1 Male2 FemaleEach column is like this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 14:00:35
|
| You should not have a table like that. That is bad design. Could you show us your schema in the form of CREATE TABLE statements.Your table should be storing 'M', 'F', instead of 1, 2.Tara |
|
|
|
NPR
Starting Member
15 Posts |
Posted - 2004-03-22 : 14:04:01
|
| Really that is bad design?Here is the table:CREATE TABLE [dbo].[dtMyProfile] ( [ProfID] [int] IDENTITY (1, 1) NOT NULL , [UserID] [int] NULL , [Gender] [tinyint] NULL , [Seeking] [tinyint] NULL , [Objective] [tinyint] NULL , [ZipCode] [varchar] (10) NULL, [Age] [tinyint] NULL , [BodyType] [tinyint] NULL , [Ethnicity] [tinyint] NULL , [Height] [tinyint] NULL , [HairColor] [tinyint] NULL , [EyeColor] [tinyint] NULL , [MaritalStatus] [tinyint] NULL , [Astro] [tinyint] NULL , [Religion] [tinyint] NULL , [HaveKids] [tinyint] NULL , [MoreKids] [tinyint] NULL , [Smoking] [tinyint] NULL , [Drinking] [tinyint] NULL , [MySpace] [tinyint] NULL , [Social] [tinyint] NULL , [FreeTime] [tinyint] NULL , [Vacation] [tinyint] NULL , [Food] [tinyint] NULL , [Car] [tinyint] NULL , [TV] [tinyint] NULL , [Spend] [tinyint] NULL , [Tattoo] [tinyint] NULL , [Piercings] [tinyint] NULL , [Flirting] [tinyint] NULL , [Kissing] [tinyint] NULL , [Affection] [tinyint] NULL , [Dancing] [tinyint] NULL , [Power] [tinyint] NULL , [Wealth] [tinyint] NULL , [Sarcasm] [tinyint] NULL , [Brainiacs] [tinyint] NULL , [Boldness] [tinyint] NULL , [Thrill] [tinyint] NULL , [WorkWeek] [tinyint] NULL , [Employment] [tinyint] NULL , [Occupation] [tinyint] NULL , [Education] [tinyint] NULL ) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 14:06:30
|
| We'll need to see your entire schema. From what I can tell from your one table, you'll need to redesign the database.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-22 : 14:07:27
|
Holy IDENTITY (Bad) SEED batman....Here we go again....Don't you think M or F would be better for describing the Sex column?Or is that for frequency? Brett8-) |
|
|
|
NPR
Starting Member
15 Posts |
Posted - 2004-03-22 : 14:14:01
|
| I haven't done the other tables that would relate to this table as of yet. I thought I would ask you guys before proceeding further. But each column would have a table that would match up with it. For instance the Hair Color table would be like this. Each column would be similar to this one.dtHairColor1 Auburn2 Black3 Blonde4 Light Brown5 Dark Brown6 Red7 White/gray8 Bald9 a little gray |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 14:20:57
|
| There are differing opinions on whether or not to create a lookup table. Some do, some don't.I would definitely not create a lookup table for those columns where the answer is Male/Female, Yes/No, etc... You should store the value in the main table.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-03-22 : 14:28:42
|
quote:
Originally posted by tduggan
There are differing opinions on whether or not to create a lookup table. Some do, some don't.I would definitely not create a lookup table for those columns where the answer is Male/Female, Yes/No, etc... You should store the value in the main table.Tara
I'd assume this is because you have either one or the other tara? Many developers like the poster in this case of this thread have learned this methodolgy because of access. Most access books recommend lookup tables such as the ones mentioned here. I think if the actual result of the column is either yes / no (2 values) then no I would not make it a lookup, but if you have more with the possibility that more values are added than I would say yes.One other thing I have noticed is some users have PKs in their databases for these lookup tables, while others don't. I don't really have an answer as to why, but if the value is unique in itself do you really need a PK?Thanks.Jon |
|
|
|
NPR
Starting Member
15 Posts |
Posted - 2004-03-22 : 14:30:57
|
| So for some of the columns if the info is small it is best to not have a separate table for the look ups? Alot of the other columns are similar to the hair color one but with longer descriptions for each ID. So I think I will need alot of look up tables. It seems awkward to do it that way but I can't figure out a better way. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 14:31:35
|
| [EDIT]Comments in reference to jhermiz's post:Have a look at byrmol's (David M's) blogs for reasons why not to have lookup tables:http://weblogs.sqlteam.com/davidm/Yes you do need a PK. But you don't need an identity column, but I'm sure that is what you meant.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-22 : 14:32:08
|
| They only int I waould have would be age...everything else should be char(1), and the case of hair color I would just store the color...How many rows are you expecting?Also, I would make the key, home phone....Done, no joins....Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 14:34:55
|
| I agree with Brett except maybe the key being home phone. Lookups should be avoided here.Tara |
|
|
|
NPR
Starting Member
15 Posts |
Posted - 2004-03-22 : 14:38:12
|
| I always thought that to have a normalized database you wanted to cut down on the redundant data. By doing what you guys are saying would not go with that. |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-03-22 : 14:38:37
|
| Hmm...Anyone got an explanation to my question?Regarding PK's used in lookups...If the actual value is unique who needs the identifier ?Jon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 14:41:02
|
I answered it already.  You don't need an identifier although you'll see a lot of environments with them (including mine but I didn't have control over it).Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 14:42:39
|
| NPR, please look at the byrmol's blog on lookup tables.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-03-22 : 14:44:43
|
quote:
Originally posted by tduggan
I answered it already. You don't need an identifier although you'll see a lot of environments with them (including mine but I didn't have control over it).Tara
I must be missing something. Some of you guys recommend not having lookup tables...but then you state that you have tables that are lookup tables without the PK. For instance lets say we have an employees table and an employee is given a type of the following values: Engineer, Purchaser, IT, Documentation, Misc.Lets say we had the employees table which basically stored employee information such as Name, Address, etc. This table also had a field for the "type" of employee. Now my question is should there be an employee type table where you have the above listed:[EmployeeTypes]EngineerPurchaserITDocumentationMisc.Or should this be just a field inside the Employees table with a check constraint?Jon |
|
|
|
Next Page
|
Table Help