user4831894
user4831894

Reputation:

How do I structure my tables and relationships in this scenario

I have 2 tables that are related using a junction table. Tbl_Items is basic information needed to create an internal "stock number" and to record info about what asset it came from. The Listings table records all the information from the listing once it is created

The current simplified tables are as follows:

Tbl_Items

ID_Items
PK - AutoNumber
Category
Short Text
Item number
Short Text
Short_Description
Short Text
Asset
Short Text
979 Motorcycle 0123456789 Kawasaki Vulcan VN 750 87-06 OEM Center Stand 34011-1126 16 - Kawasaki 1993 Vulcan 750 - VN750 -

Tbl_Listings

ID_Listings
PK - AutoNumber
ID_Items
Number
Item number
Short Text
Title
Short Text
Start price
Currency
Start date
Date/Time Extended
Make
Short Text
Model
Short Text
Part Number
Short Text
1243 979 0123456789 Kawasaki Vulcan VN 750 87-06 OEM Center Stand 34011-1126 $49.00 Aug-25-22 11:24:30 PDT Kawasaki Vulcan 34011-1126

Tbl_Import_Active_Listings

Item number
Short Text
Title
Short Text
Start price
Number
Start date
Short Text
Make
Short Text
Model
Short Text
Part Number
Short Text
0123456789 Kawasaki Vulcan VN 750 87-06 OEM Center Stand 34011-1126 49 Aug-25-22 11:24:30 PDT Kawasaki Vulcan 34011-1126

The current work flow is as follows

At the moment step 3 is limited in detail because of the time it takes. But capturing all of the listing data is important to streamline future listings.

I was advised to use a junction table and remove the Item Number from the Items table to not only prevent duplicate data in tables, but to prepare for a time where a many to many relationship would likely exsist between Items and Listings.

To test this I created the following junction table and removed the Item_Number from the Items table

Tbl_Junction_Items_Listings

ID_Items PK ID_Listings PK
Joined On Joined On
Tbl_Items.ID_Items Tbl_Listings.ID_Listings

I'm not sure the junction table is appropriate in this case because as I see it there is really only a one to many relationship between Items (one) and Listings (many)

Ultimately I need to know if I'm missing something about the need for the junction table.

Most importanly, if I do need the Junction table, then how do I associate the listing back to the item when importing if I don't keep the item number is both tables

UPDATES:

I also have an Orders table which does require a many to many relationship with the Listings table. Would that be the reason it was suggested to use one for listings and items?

An Item can have multiple Listings when they are listed with multiple providers.

Consequently the Item Number for each listing will be different for each provider. That's why I'm not sure how to match them up when I import them at day end

Upvotes: 0

Views: 61

Answers (0)

Related Questions