I just want to ask you guys if the database design that i made for my asp.net website is correct or not. So basically, I created a database for the brands of my guitar products, which is like this:Database for BrandsCode for Database Brands:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[guitarBrands]([id] [int] NOT NULL,[type] [varchar](50) NOT NULL,[name] [varchar](50) NOT NULL,[image] [varchar](255) NULL,PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (1, N'Guitar', N'Ibanez', N'../Images/Guitar Brands/ibanezLogo.jpg')INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (2, N'Guitar', N'Fender', N'../Images/Guitar Brands/fenderLogo.jpg')INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (3, N'Guitar', N'Gibson', N'../Images/Guitar Brands/gibsonLogo.jpg')INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (4, N'Guitar', N'PRS', N'../Images/Guitar Brands/prsLogo.jpg')INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (5, N'Guitar', N'Musicman', N'../Images/Guitar Brands/ernieballLogo.jpg')INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (6, N'Guitar', N'ESP', N'../Images/Guitar Brands/espLogo.jpg')and i also have another database for the guitar models within that brand.Database for Guitar models(I hope the image is clear)And here is also the code for database guitar models: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[guitarItems]( [id] [int] IDENTITY(1,1) NOT NULL, [type] [varchar](50) NOT NULL, [brand] [varchar](50) NOT NULL, [model] [varchar](50) NOT NULL, [price] [float] NOT NULL, [itemimage1] [varchar](255) NULL, [itemimage2] [varchar](255) NULL, [description] [text] NOT NULL, [necktype] [varchar](100) NOT NULL, [body] [varchar](100) NOT NULL, [fretboard] [varchar](100) NOT NULL, [fret] [varchar](50) NOT NULL, [bridge] [varchar](100) NOT NULL, [neckpickup] [varchar](100) NOT NULL, [bridgepickup] [varchar](100) NOT NULL, [hardwarecolor] [varchar](50) NOT NULL, PRIMARY KEY CLUSTERED  ( [id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =  OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[guitarItems] ON INSERT [dbo].[guitarItems] ([id], [type], [brand], [model], [price],  [itemimage1], [itemimage2], [description], [necktype], [body], [fretboard],  [fret], [bridge], [neckpickup], [bridgepickup], [hardwarecolor])  VALUES (1, N'Guitar', N'Ibanez', N'ARZ307', 9000.00, N'../Images/Guitar  Brands/Guitar Items/ibanezARZ307.jpg', N'../Images/Guitar Brands/Guitar  Items/ibanezARZ307StandingPosition.png', N'Its a Les-Paulish shaped guitar  with a black body and head stock. This is a seven string guitar tuned  BEADGBe from low to high. The grade and quality of this guitar is  exceptional compared to some other brands out there.', N'ARZ Mahogany SetIn  Neck Smooth Heel', N'Maple Top/Mahogany Body', N'Bound Rosewood Fretboard  W/White Block Inlay', N'24 Medium Frets', N'ART-1 Bridge', N'Classic Elite  Neck Pickup Passive/Ceramic', N'Classic Elite Bridge Pickup  Passive/Ceramic', N'Chrome') INSERT [dbo].[guitarItems] ([id], [type], [brand], [model], [price],  [itemimage1], [itemimage2],[description], [necktype], [body], [fretboard],  [fret], [bridge], [neckpickup], [bridgepickup], [hardwarecolor]) VALUES (2,  N'Guitar',N'Ibanez', N'DT420TCR', 11000.00, N'../Images/Guitar  Brands/Guitar Items/ibanezDT420TCR.jpg', N'../Images/Guitar Brands/Guitar  Items/ibanezDT420TCRStandingPosition.png', N'The Destroyer Series DT420 is  made from mahogany with a transparent cherry sunburst gloss finish. The  single cutaway solid body harks back to the original 1975 design,  delivering maximum resonance and sustain.', N'Destroyer Mahogany Neck',  N'Mahogany', N'Bound Rosewood Fingerboard with Acrylic and Abalone Block  Inlay', N'22 Jumbo Frets', N'Full Tune III', N'Classic Elite  Humbucker,Passive/Ceramic', N'Classic Elite Humbucker, Passive/Ceramic',  N'Chrome') INSERT [dbo].[guitarItems] ([id], [type], [brand],[model], [price],  [itemimage1], [itemimage2], [description], [necktype], [body], [fretboard],  [fret], [bridge], [neckpickup], [bridgepickup], [hardwarecolor]) VALUES (3,  N'Guitar', N'Ibanez', N'JBM100', 18000.00, N'../Images/Guitar Brands/Guitar  Items/ibanezJBM100.jpg', N'../Images/Guitar Brands/Guitar  Items/ibanezJBM100StandingPosition.png', N'Signature model guitar from Jake  Bowen of Periphery. Due to their heavy progressive sound, He decided to  move the boundaries of guitar design and performance with his new signature  guitar. This guitar is the main definition of tight and aggressive.', N'JBM  3pc Maple/Bubinga neck', N'Maple top/Mahogany body', N'Bound Ebony  fretboard w/JBM special inlay on 12th fret', N'24 Jumbo frets w/Prestige  fret edge treatment', N'   Lo-Pro Edge tremolo bridge', N'DiMarzio Titan  (H) neck pickup (Passive/Ceramic)', N'DiMarzio Titan  (H) bridge pickup  (Passive/Ceramic)', N'Black (Gold machineheads)') SET IDENTITY_INSERT [dbo].[guitarItems] OFFactually there are three more columns but it doesn't fit in the image but you can easily identify it in the code. Also i did not include all the code for guitar models because it is too many to show it here.Please kindly advice on what is the best approach to creating a database design based on the details given above. By the way, this is just for a personal project and will be used for learning purposes only. But I might use this in my portfolio if all goes well.
Schema for SQL database of guitars
sql;database;t sql
If you're wanting to practice doing RDB correctly, I'd suggest getting very used to ensuring your schema are fully normalized.In this case, you half-way did it. You have your brands table and a guitars table but the relationship between the two is based on a column other than the brands table's PK.Change your guitarItems.brand varchar to an int and add the foreign key relation on guitarBrands.id . That way, if the varchar ever changes, your relational model isn't borked. Your case might not be a good example, except in the rare instance of a brand changing names. But it's much more common for other strings that can change frequently (or for simple typo-protection).The same goes for the guitar type. Make a new table for types with an AI PK and the type's display name. Then normalize your guitars table to use that PK.That could be extended as far as you see fit. Other candidates would be necktype, body, fretboard, etc. Any place you see a lot of repeating or standardized data is a good candidate for being broken off into a separate table. Not always, but it's a good first indication.On top of this preserving your relational model in the case of data revisions, it can significantly speed up lookup times. For instance, if you can query   WHERE foreign_key_int = 2rather than   WHERE foreign_key_varchar = 'Some really long name'you can see real benefits. Especially if you're doing more complex joins or large IN queries.This also has the benefit of saving you space with each record. If you're fully normalized, you're only storing a handful of bytes for the foreign keys, rather than duplicating long strings over and over. For larger databases, that can be the difference between the database flying or grinding to a halt.Aside from the normalization, I'd also recommend you get in the habit of not storing full file paths in your database. File system hierarchies change all the time and you never know what the working directory is of whatever is reading your database records.If you store the images in \path\to\images\logo.png, store logo.png in your column and then define in code how to get to that image. The code executing that query is the only one that knows what its working directory is. So decouple your data layer and your business/presentation layers by letting it build out that path at run-time.As well, if your hierarchy ever changes (say, you want to store all your images under a new folder or on a cloud host like AWS), you only have to change your one or two functions in the code base. If you stored the path in the DB, you'd have to go messing with the data by updating every single record.