CODEWITHSUNDEEP
mysql
hojkoff
hojkoff

Reputation: 205

MySQL: Key field value unique to 2 tables

I'm looking to have 3 tables in a MySQL database that control, say items for sale in an online shop. The 3 tables are as follows:

  1. Current items
  2. Discontinued items
  3. Invoicing

I've split the items into 2 tables to prevent the website having to look up the entire current and discontinued items when listing the sale items on the site. The website only needs to list the current items so I think doing this will speed up performance as the site grows. The query to list stock will be run frequently.

Once items are not longer for sale, they will be 'decanted' into the discontinued table and new items will be added to the current items table.

Each invoice will refer to a unique key field of the items databases (current or discontinued) to identify what items were on that invoice.

In order for this to work, when a new item is created and added to the current items table, the key field will need to be unique to both tables. Otherwise, the invoicing will potentially reference 2 items as it looks through both the current and discontinued tables to work out what items were on each invoice.

Is there a way to assign a unique value to a field that is unique to 2 different tables?

Many thanks in advance.

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This seems like a poor design.

Instead, just have a discontinued flag in an items table -- perhaps along with DiscontinuedDate.

This will allow you to maintain referential integrity. It will simplify queries, because an item reference will always go to one table.

If you like, you can then create two views on the items table: v_ItemsCurrent and v_ItemsDiscontinued. These would use a where clause to choose the appropriate set of items.

Upvotes: 1

Related Questions