0

I am trying to create a database for an internal store that we are developing to manage our internal items (like phones, computers, office equipment, etc). The issue Ive run into is that they want each item to be able to support add-ons. The only issue I have with this is they also want the prices to be dynamic based on your region for both items and addons. So a different region may have a different price than some other region. The Items table right now supports this through a foreign key to the Availabilities table. What is the best practice to get the Addon's table to be a foreign key on the Availabilities table so that it to can have multiple prices per region? Is my design totally messed up here?

0

You'll can solve the problem by adding more linking tables. You can alter your design to resemble something like this:

CREATE TABLE Items
(
    itemID INT PRIMARY KEY IDENTITY(1,1)
    ,itemDetails NVARCHAR(100)
)
GO
CREATE TABLE Regions
(
    regionID INT PRIMARY KEY IDENTITY(1,1)
    ,regionName NVARCHAR(100)
)
GO
CREATE TABLE AddOns
(
    addOnID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnDetails NVARCHAR(100)
)
GO
CREATE TABLE ItemAddOns
(
    itemAddOnID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT
    ,addOnID INT
    ,CONSTRAINT [FK_ItemAddons_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)
    ,CONSTRAINT [FK_ItemAddons_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
)
GO
CREATE TABLE ItemAvailability
(
     itemAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT 
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_ItemAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_ItemAvailability_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)  
)
GO
CREATE TABLE AddOnAvailability
(
    addOnAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnID INT 
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_AddOnAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_AddOnAvailability_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
) 

Something like that setup will allow you to easily query the cost for any Items and AddOns by any region. If AddOns are not restricted to particular Items you can omit the ItemAddOns table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.