| Author |
Topic |
|
trusted4u
Posting Yak Master
109 Posts |
 Posted - 2003-05-27 : 00:54:08
|
| Hi,If I create 1 variable of type varchar(20) and another of type tinyint which one will occupy more space ?I want to create a table which contains many fields with repeated values for eg: Colour,car_make,model_name,fuel_type etc. So is it advisable to store the values exactly as it is or store just flags [i.e. numbers ] which maps to the actual values in another table ?Thanks,-Marjo. |
|
|
trusted4u
Posting Yak Master
109 Posts |
Posted - 2003-05-27 : 02:03:28
|
 |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-05-27 : 03:03:54
|
| Hey Marjo:A variable of varchar(20) theoretically uses one byte per character in the variable. So, if the variable contains "Hello", the variable uses only 5 bytes and not 20. Tinyint needs only one byte, but is restricted to a max value of 255.But I really dont see what this has to do with your table structure...shouldnt you be storing the primary key to the other table. It is one of the basic rules of normalization, remove all repeating groups and redundant data. Of course the datatype of the foreign key column will depend on the datatype of the primary key of the other table, so if the primary key is of type varchar(20), there is nothing wrong with having your foreign key column of type varchar(20). I dont think you can save space at the cost of referential integrity.OS |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-05-27 : 03:08:55
|
| Marjo,For data type sizes look up BOL.It is adviseable NOT to store repeating values in columns. Stop what you are doing and go back to the design phase. Finally you should store the actual value in the column, but that can/is debatable.DavidM"SQL-3 is an abomination.." |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-27 : 03:22:35
|
| The varchar actually needs it's length + 2 bytes overhead. So 'Hello' would take up 7 bytes :)Marjo, I would advice you to read a book on database design. Professional SQL Server 2000 Database Design by Louis Davidson (wrox) is quite good. |
|
|
|
trusted4u
Posting Yak Master
109 Posts |
Posted - 2003-05-27 : 07:47:07
|
| Thanks guys for your suggestions.I know that as per the normalization rules the repeated values must be stored in other table. Lets say if I have 5 fields with repeated values then I will have to create 5 other tables for referential integrity. So the most troublesome scenario that I can forsee is the report. As I had mentioned that I have many repeated values in many fields so while designing reports I will have to write huge SQL statements [Joins] to make an understandable and readable record. So the question is, eventually woudn't the SQL statements go slow ?? -Marjo. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-27 : 08:08:02
|
| No, you do not need five other tables to store the attributes, you can do it like this:CREATE TABLE Attributes (ID int, AttribName varchar(20), AttribValue varchar(50))INSERT INTO Attributes (ID, AttribName, AttribValue) VALUES (1, 'Color', 'Red')INSERT INTO Attributes (ID, AttribName, AttribValue) VALUES (1, 'Make', 'Ford')INSERT INTO Attributes (ID, AttribName, AttribValue) VALUES (1, 'Model', 'Mustang')While you CAN do that, the real problem, as others stated, is your design. Why not just have a Cars table with those columns in it? They're NOT repeating groups because they are distinct attributes. Every car has a make, model, color, fuel type, and so on, so it makes perfect sense to keep them in one table. As far as whether or not to use tinyint values instead of varchar, that's up to you, but if you have to join 5 tables together to get meaningful information from a query, that's not necessarily a good design either, regardless of how much space you save. |
|
|
|
trusted4u
Posting Yak Master
109 Posts |
Posted - 2003-05-28 : 02:57:53
|
| Thanks Robvolk. According to your eg. I think the query to get the complete information would be like this :SELECT VEHSRLNO,A.NAME COLOUR,B.NAME MAKE,C.NAME MODELFROM VEHICLE,ATTRIB A,ATTRIB B,ATTRIB CWHERE A.ID = VEHICLE.COLOUR ANDB.ID = VEHICLE.MAKE ANDC.ID = VEHICLE.MODEL But with this approach also we are making 3 copies of ATTRIB table. So what is the advantage or difference of this approach over using 3 sepearate tables ? Becoz if I use 3 seperate tables then too my query will remain the same with just slight changes.- Marjo |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-05-28 : 03:13:11
|
| The query would look something like this SELECT VEHSRLNO,A.attribvalue as color,B.attribvalue as MAKE,C.attribvalue as MODEL FROM VEHICLE,ATTRIBUTES A,ATTRIBUTES B,ATTRIBUTES C WHERE A.ID = VEHICLE.vehsrlno AND a.attribname = 'color' ANDB.ID = VEHICLE.vehsrlno AND b.attribname = 'make' ANDC.ID = VEHICLE.vehsrlno and c.attribname = 'model'You're not really "making" copies of the table, you're simply reading it three times. I'm not really sure what you want to gain by putting the different attributes into different tables.-------Moo. |
|
|
|
trusted4u
Posting Yak Master
109 Posts |
Posted - 2003-05-28 : 03:44:06
|
quote:
You're not really "making" copies of the table, you're simply reading it three times
I thought it makes 3 copies and therefore I was confused over the comparison between this approach and seperate tables. But now the concept is clear and I will follow the same eg. [suggested by Robvolk].Thanks mist and everybody for your help and suggestions.Best Regards- Marjo. |
|
|
|
|
Field Datatype and Size Question !