CODEWITHSUNDEEP
sqlasp.net-mvc
Justin Adkins
Justin Adkins

Reputation: 1234

Need advice: SQL database design

I'm getting ready to start the design of a database for a ASP.NET MVC application and I had a quick question about the best way to store information in a table.

In the old version of my database, I had a table called Phones and It had around 40-50 different columns.

Rather than having so many columns in a table, would you recommend keeping the "essential" data in the Phones table and then creating an additional table called PhoneDetails with the rest of the data?

Thanks for your input!

Update

Here are the columns that are in my table: (there are 59 total)

       [PhoneID]
      ,[Make]
      ,[Model]
      ,[ProcessorSpeed]
      ,[Dimensions]
      ,[Weight]
      ,[RAM]
      ,[Storage]
      ,[BatterySize]
      ,[TalkTime]
      ,[StandbyTime]
      ,[DisplayType]
      ,[Resolution]
      ,[DisplaySize]
      ,[RearCamera]
      ,[FrontCamera]
      ,[MinOS]
      ,[MaxOS]
      ,[NFC]
      ,[Notes]
      ,[Publish]
      ,[FullName]
      ,[Date]
      ,[Colors]
      ,[Sensors]
      ,[SIMCardType]
      ,[ChargingConnectors]
      ,[AVConnectors]
      ,[SystemConnectors]
      ,[USBVersion]
      ,[BluetoothVersion]
      ,[BluetoothProfiles]
      ,[WiFiVersion]
      ,[WiFiSecurityModes]
      ,[WirelessConnectivity]
      ,[GSMTypes]
      ,[GSMUpload]
      ,[GSMDownload]
      ,[LTETypes]
      ,[LTEUpload]
      ,[LTEDownload]
      ,[WCDMATypes]
      ,[WCDMAUpload]
      ,[WCDMADownload]
      ,[BatteryVoltage]
      ,[RemoveableBattery]
      ,[MaxMusicPlayback]
      ,[MaxVideoPlayback]
      ,[MaxWiFiTime]
      ,[ProcessorName]
      ,[ProcessorType]
      ,[RearAperture]
      ,[RearFocalLength]
      ,[RearMinFocusRange]
      ,[FlashType]
      ,[FlashOperatingRange]
      ,[FrontResolution]
      ,[FrontAperture]
      ,[DeviceGeneration]

Upvotes: 0

Views: 44

Answers (4)

Avi Turner
Avi Turner

Reputation: 10456

40-50 columns sounds like allot of columns, more than I can imagine required for a "phones" tables, but without further information about what those columns are, It is really hard to give carved in stone truths.

In general if your table looks something like this:

Id Motorola Nokia LG HTC ...many more...
----------------------------------------
1     1      0    0   0     .....
2     0      0    0   1     .....
3     0      1    0   0     .....
4     1      0    0   0     .....
5     0      0    1   0     .....

you definitely should normalize your DB to something more like:

Phones:

Id   PhoneTypeId
---------------
1     1    
2     4    
3     2    
4     1    
5     3

PhoneType:

Id   TypeName
---------------
1     Motorola    
2     Nokia    
3     LG    
4     HTC

Of course, this is just an example, but this is the best I can offer without further information regarding the nature of the columns is Phones table.

Upvotes: 1

mohsensajjadi
mohsensajjadi

Reputation: 335

You should think about normalizing your one table if needed and if the table really has relationships, otherwise there is not much to be gained by just splitting a table into two separate tables. Think about are there redundant data in any of the columns?

In that case you can have a separate table with that info and a FK relationship between them.

Upvotes: 0

Yousuf
Yousuf

Reputation: 3285

I would consider splitting into 2 tables. If it meets any of the below 2 conditions.

If there are large number of rows in some columns with NULL values.

or

If your application is accessing essential columns more often than the remaining columns.

Upvotes: 0

Sinoy Devassy
Sinoy Devassy

Reputation: 574

Its good to create another table for save the phone details with FK reference, because there have so many fields.

Upvotes: 0

Related Questions