July 10, 2010 at 2:10 pm
Hello All. I've ran into a mental wall and I can't figure out a good way to design a set of tables based on this diagram.
My attempt to describe the diagram --- lets say we have a device (register) that is only allowed to scan items from certain areas of a store. So Registers are allowed access to one or more ProductSections that each have individual Products. On top of that, we have to store the Tax that is applied to Products BY Register, since Registers can be in different Locations. Also, not all products will have taxes...
I ended up with a solution, but it's not quite complete and I don't think it's the proper way to be doing it...
- A mapping table that shows the relationship between ProductSesctions and their individual Products, with an Identity as a PK.
- A RegisterSetup table that had a composite key on the RegisterID and the mapping table Identity, along with a TaxID FK to the Tax that needs applied to that particular Register and ProductSection/Product mapping....This is where I start to feel lost...
I'm also missing the relationship between a Register and it's ProductSections...
Anyone have any suggestions? Is there a better approach I should consider?
July 11, 2010 at 9:17 am
A couple questions:
Is tax specific (1:1) to a product and register? Or just a product? Meaning if I went to a different register could I potentially have different tax? If not, I'm not sure tax has a linkage to register, just product. Since products are linked to specific register(s), through product section, taxes would have an indirect linkage.
I think, and I'm not sure, that you want to have
Products < -> ProductSection < -> Section
as a linkage. Meaning that ProductSections have a FK to ProductSections and Sections have an FK link to ProductSections. I would have ProductSection as a two column table.
Create table Products
( productid int
, Productname varchar
, ...
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[productid] ASC
) )
go
create table Section
( SectionID int
, SectionName varchar
, ...
CONSTRAINT [PK_Section] PRIMARY KEY CLUSTERED
(
[sectionid] ASC
))
go
Create table ProductSection
( ProductID int
, SectionID int
)
go
ALTER TABLE [dbo].[ProductSection] WITH CHECK ADD CONSTRAINT [FK_ProductSection_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([productid])
GO
ALTER TABLE [dbo].[ProductSection] CHECK CONSTRAINT [FK_ProductSection_Products]
GO
ALTER TABLE [dbo].[ProductSection] WITH CHECK ADD CONSTRAINT [FK_ProductSection_Section] FOREIGN KEY([SectionID])
REFERENCES [dbo].[Section] ([SectionID])
GO
ALTER TABLE [dbo].[ProductSection] CHECK CONSTRAINT [FK_ProductSection_Section]
GO
This gives you a linkage, and then you can do the same thing between Section and Register, link them with a RegisterSection table. It can be a lot of joins, but it provides flexibility, and the intermediate tables (ProductSection and RegisterSection) will be small and quick to join. They'll likely live in memory. You also don't have to join through to Section from Products to get the list of products for a section. If you have the section ID, you just need the intermediate table.
July 11, 2010 at 12:37 pm
Thanks for the reply. To answer your question, yes there can be different taxes on different registers. That's the part that had me stumped. How do I link up a tax to a register and a product when that link doesn't exist to begin with?
In your example, for the ProductSection join table, is the PK a composite key on both productID and sectionID, or does it have it's own identity column as a PK (or is that unnecessary?)?
July 11, 2010 at 12:43 pm
In my example, I hadn't built a PK, but you can add one to ProductSection. Or a constraint to prevent duplicates. Not sure a PK matters.
In terms of taxes/registers. My guess is that you would have a Tax/Product/Register table that links those three items up. It would have the PKs of those 3 tables with the tax rate. Of course, you'll have to populate it with the various tax rates.
July 12, 2010 at 9:20 am
Are the Register to tax relationships based on something like Register Region (or perhaps based on Country, Smaller Region, Even Smaller Region, Tiny Region, Little Tiny Region, and so on? Or do you buy, perhaps, postal/regional code to aggragated tax value data from a vendor)?
Are the Product to tax relationships based on some kind of category - Food, Luxury, Standard, Subsidised A, Subsidised B, Medical, etc. etc.?
I.e. do you really need to add specific values for every single new Product and Register... or do you really want to? Which will be better after a few years of rapid growth? Which will prevent rapid growth in the first place?
July 13, 2010 at 8:26 am
Is the tax variance by register based on a single rate for the geographical location of that register?
In other words, the sales tax for State 1 is .0825; for State 2 it is .075, etc. So the tax rate varies by location, but products are either taxable by that rate or not?
If so, I would store the tax rate in the Register table (or, if you're more normalized in the Location table). And in the Products table I would have a IsTaxable bit (as Nadrek suggests, if the IsTaxable characteristic is actually applied by Produc Category, i.e. Food is not taxable, then this bit would probably belong in the Category table).
If Product.IsTaxable = 1 (or ProductsCategory.IsTaxable = 1) then apply the tax rate from the Locations table.
July 13, 2010 at 8:47 am
Very seriously, you should contact the appropriate Accounting or Finance group or person; taxation can get very complex, and governments get... upset... when it's not paid properly.
Note that rates change; not just permanently (and then watch out for correctly calculating backdated or future dates purchases/returns), but also temporarily (Sales Tax Holiday on Back To School items).
Check to see if items can be in multiple categories... and if so, what the order of precedence for taxation is. Likewise, registers in multiple jurisdictions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply