Multiple tables sharing an id list from a comon table.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Barely an Access user, hacking away at creating a database.
I confused myself, and am hoping someone can help un-confuse me. ;-)

Let me try to explain:

I have a database with many tables, each representing a different type of
item.
For simple example:
Table: BOX
idbox
 
What is the definition of BOX and BUCKET?

Can an item be a BOX and a BUCKET?

My gut instinct is that you need three tables depending on the answer
to my last question above:

types:
type_id - Autonumber (PK)
type_name - text (Unique) = BOX, BUCKET, PAIL, etc.

items:
item_id - Autonumber (PK)
barcode_num - text (Unique)
... more info about item ..

item_types:
item_id - FK to items(item_id)
type_id - FK to types(type_id)
... more info about this combination ..


If each item can only be one type, then item_id should be the primary
key in item_types, otherwise (item_id, type_id) should be the primary
key.

Cheers,
Jason Lepack
 
Thanks for the feedback!

To answer:

BOX and BUCKET are just simple examples. In reality I have many "item"
tables which track widely varying data about each item. Each thing can only
belong to one "item table". So BOX cannot be BUCKET.
 
I guess the question really comes down to how you intend to use this.

Without more information I can only guess, but you might stay along
the path that you're currently on.

Items:
item_id
barcode_num
all other info that pertains to "ALL ITEMS"

Buckets:
item_id - relates to Items.item_id (unique)
info specific to buckets

Boxes:
item_id - relates to Items.item_id (unique)
info specific to boxes

You will need to use VBA on your forms to ensure that an item is
one(and only one) of the item types. You would be able to use
triggers to enforce this in SQL Server. (wait, I don't understand
your second post... you are or you aren't using an SQL Server back
end?)

Cheers,
Jason Lepack
 
The database is SQL Server.

Jason Lepack said:
I guess the question really comes down to how you intend to use this.

Without more information I can only guess, but you might stay along
the path that you're currently on.

Items:
item_id
barcode_num
all other info that pertains to "ALL ITEMS"

Buckets:
item_id - relates to Items.item_id (unique)
info specific to buckets

Boxes:
item_id - relates to Items.item_id (unique)
info specific to boxes

You will need to use VBA on your forms to ensure that an item is
one(and only one) of the item types. You would be able to use
triggers to enforce this in SQL Server. (wait, I don't understand
your second post... you are or you aren't using an SQL Server back
end?)

Cheers,
Jason Lepack
Click to expand...
 
If you're in SQL Server then you just need to use triggers on Boxes
and Buckets to prevent the insert:

create table boxes (
barcode_num char(4) primary key,
box_num smallint identity(1,1)
)

create table buckets (
barcode_num char(4) primary key,
bucket_num smallint identity(1,1)
)
go

create view dbo.barcodes
as
select barcode_num from dbo.boxes
union all
select barcode_num from dbo.buckets
go

create trigger box_barcodes on boxes instead of insert, update
as

if exists (
select
i.barcode_num
from
inserted i
left join barcodes b on b.barcode_num = i.barcode_num
left join deleted d on d.barcode_num = b.barcode_num
where
b.barcode_num is not null
and d.barcode_num is null)
begin
PRINT 'Boxes: Insert / Update Aborted: Duplicate Barcode'
end
else
begin
delete
boxes
from
boxes b
join deleted d on d.barcode_num = b.barcode_num

insert into
boxes
select
barcode_num
from
inserted
end
go

create trigger bucket_barcodes on buckets instead of insert, update
as

if exists (
select
i.barcode_num
from
inserted i
left join barcodes b on b.barcode_num = i.barcode_num
left join deleted d on d.barcode_num = b.barcode_num
where
b.barcode_num is not null
and d.barcode_num is null)
begin
PRINT 'Buckets: Insert / Update Aborted: Duplicate Barcode'
end
else
begin
delete
buckets
from
buckets b
join deleted d on d.barcode_num = b.barcode_num

insert into
buckets
select
barcode_num
from
inserted
end
go

insert into
dbo.boxes (barcode_num)
select
'1111'
union all select '1112'
union all select '1113'
union all select '1114'
union all select '1115'
union all select '1116'
union all select '1117'
union all select '1118'


update
boxes
set
barcode_num = convert(char(4), convert(int, barcode_num) + 1)
from
boxes

insert into buckets (barcode_num) values ('1118')


select * from boxes
select * from buckets

drop view barcodes
drop table boxes
drop table buckets

Cheers,
Jason Lepack
 
I'm coming late to this thread but I believe that you're heading in
the wrong direction. You most definitely should not have a separate
table for each *item type*

Your "main" table should be something like tblItem. Every item in
your application should be listed in that table. The *type* attribute
can be filled from a lookup table (say tblIType) that lists every
item type you cover: box, bucket, crayon, PC, ....

For a PrimaryKey in tblItem I'd use an Autonumber datatype. If you
generate the barcodes then I'd use the "DMax()+1" paradigm as the
*default* value to generate sequential barcodes that will be unique
across the application.

As you are entering data for a new item you'd pick the item type from
the lookup table referenced above.

HTH
 
Part my issue here is that each type of item has a it's own very specific
list of characteristics that I need to track as well. So my original intent
was to keep those characteristics as columns in each "item type table".
 
Back
Top