Proper Way to Add a New Product Type

[Elysium]

Here's the scenario:

I have a bookstore that sells various items. I've broken the types out into 5 groups:

01) Books
02) Stationary
03) Apparel
04) Media
05) Trinkets

I have a pretty good handle on how to deal with adding a new type of apparel to my inventory. What I can't figure out is how to add a totally new line of merchandise without having to go underneath the hood of the application, add a new table and establish relationships. My goal is to have a simple process to add a new category type from the 'front-end'. For example, if I wanted to start selling Concert Tickets, how could I add an 'Events' table and subsequent tables that hold the description for the events?

Randy

 

[lespaul]

You shouldn't. You should have ONE table that lists all your items. You should have a field in this table that indicates what kind of item it is, BOOK, Stationary, Apparel, etc.

You should read 'The Fundamentals of Relational Database Design', it is a must read for anyone working on databases.

leslie

 

[Elysium]

I did read that article and it was good. But it didn't answer my question directly. In my scenario, a concert ticket would have to have detail such as date, artist, type and possibly other fields. These fields are different from the apparel or media descriptors. And therein lies my question. If a user wantetd to add a brand new category to the application, is there a way to have a details table added by their own actions rather than me actually adding the table?

Randy

 

[lespaul]

No, the EVENT has detail such as date artist type etc.

The TICKET has details such as cost, price and distributor just like all the other items you sell have cost, price and distributor.

Leslie

 

[Elysium]

In the same vein, I would still have to create these tables because they are inherently different from a book, media (such as CD, tape, video), apparel, etc. Is this possible without coding from the user's prespective?

 

[willir]

Hmmm

It seems you have more than one system.

The first, is a re-sale business where you sell inventory - Leslie, is absolutely correct in that you just need an Inventory table, and then select which inventory item you are selling.

Selling tickets, especially if arranging bookings, is a different system with different needs. You can treat tickets like "inventory" if you don't need to track dates, but it sounds like you do need to track dates.

Then, it seems like you also want to sell / rent media. If selling media, then just treat it as another inventory item. Different ball of wax if you are renting.

...Moving On

A simple ticket system.

tblEvent
EventID - primary key
EventName
EventDate
SalePrice
NumOfTickets

Discussion:
This will allow you to add events when required. This system does not allow you to sell tickets for different seats. Ticketing systems can get pretty complex with seat reservations, tiered pricing, discounts, etc.

Now a simple way to incorporate this with your current system is to assume you have point of sale system along the lines of...

tblOrder
OrderID - primary key
PurchaseDate
Paytype

tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to Order table
ItemID - foreign key to Item table
ItemQty
ItemPrice
ItemExtPrice

Now, you can either add a field to the OrderDetail table for the EventID, or perhaps a better solution would be to include a OrderEventDetail table which would have a similar design to the OrderDetail table...

tblOrderEventDetail
OrderEventDetailID - primary key
OrderID - foreign key to Order table
EventID - foreign key to Event table
ItemQty
ItemPrice
ItemExtPrice

Presentation could involve using a tab subform - OrderDetail on one tab, OrderEventDetail on the other.

The order total would include both detail tables.

Richard

 

[Elysium]

I guess using tickets veered everyone from my problem and I apologize for that. A better example would be this: Let's say that I initially only sell the following categories:

01) Books
02) Stationary

After I've made money and have seen a need to sell DVDs, CDs, etc, I want to start stocking this type of inventory. The system I currently use doesn't have any provisions to store 'media'. The inventory DB includes these tables (fields absent for arguments sake):

tblInventory
tblBookDetail
tblStationaryDetail
tblSuppliers

My question is this: is it possible and good application design to allow the user to add this entirely new product line, media, through some sort of GUI? Or do I have to go back into the guts of the system, add the detail table and set all relationships. I don't believe I can have a catch all table if I want to stay in the bounds of normalization.

Thank you for everyone's input and patience with this. Both you of have shared insightful tips that will be used regardless.

Randy

 

[willir]

I would caution against this -- from the design view.

When you create a new table, you need to create relationships. Then there are indexes, primary key, etc. - This type of thing should only be done by the developer.

But let's step back a bit.

Would be kind enough to supply the table structure for Iventory, and more importantly, bookDetail, StationaryDetail and Supplies.

I want to see what you are trying to do.

Richard

 

[Elysium]

Richard,

I will do that. Give me some time to put it together here. If I don't get back to you today, you'll definitely see a post from me on Monday. Thanks,

Randy

 

[willir]

Not a problem --enjoy the weekend.