Design Help

[Kaz]

I am not very experienced at designing a database as I
only input data before or created a small database. I
now need to create a database with the following
information and can't get my head round the tables and
relationships. Could anyone help with the following
information.
It is a Leaflet Database and will consist of:

Name of Leaflet
Number of Leaflet - e.g. r01, g01, so13 etc
Publisher - Many Publishers
Year
Category (Category will consist of 7 sections with some
falling into more than 1 category at times)
Summary - Brief breakdown of info in leaflet
Any help with the breakdown of tables will be very much
appreciated.

 

[rpw]

Try this to start with,

tblPublishers
PublisherID
PubName
PubAddress
(other fields, that describe 'publisher')

tblCategory
CategoryID
CategoryName
Description

tblLeaflet
LeafletID
PublisherID
LeafletNumber (looks like you need to be able to put whatever the publisher did)
LeafletDate
Summary

tblLeafletCategory
LeafletID
CategoryID

This last table is a junction table for a M:M relationship - one leaflet may have many categories and one category may have many leaflets. This table will store all of those relationships.

 

[Kaz]

Many thanks, I have got started at last. One more
question if you don't mind, In the category section I
need a code for each leaflet eg a01>, G01>, SO01>, R01>
etc how should I do this, I have done a drop down list
for the category name what should I do for the
Letter/number ID? Should I have 2 fields for this eg
category ID for the letter and another for the number?
Hope you can help

-----Original Message-----
Try this to start with,

tblPublishers
PublisherID
PubName
PubAddress
(other fields, that describe 'publisher')

tblCategory
CategoryID
CategoryName
Description

tblLeaflet
LeafletID
PublisherID
LeafletNumber (looks like you need to be able to put whatever the publisher did)
LeafletDate
Summary

tblLeafletCategory
LeafletID
CategoryID

This last table is a junction table for a M:M

relationship - one leaflet may have many categories and
one category may have many leaflets. This table will
store all of those relationships.

 

[rpw]

Hi Kaz,

I'm sorry, but I'm not quite sure I undersatnd what you are asking for, so I have a few questions for you before I supply an answer OK?

By "category section" do you mean the category table or the junction table? Or a form?

Who creates these leaflet codes - you, the publisher? Do the letters and numbers actually mean something? Are you wanting to use the "leaflet number" as the primary key ID or as a descriptive field?

It seems to me (from your post) that it's possible that you and I are "not on the same page" with ID's and PK's so I'll add a little clarification.

In the table structure I suggested, I neglected to identify the primary keys but typically, I listed the PK first and my intent was that the PK would be an autonumber. In most cases, the autonumber is never viewed by the user. In the case of the leaflets, it appeared to me that the "leaflet number" was some code that the publisher attached to it and it did not seem that it could be guaranteed unique over time, so I added "leaflet number" as a descriptive field, and added a separate ID (PK) field.

Please post back and I'll try to assist further.

rpw

 

[rpw]

Ooops, it seems that I forgot to add a "Name" field in the leaflet table. I'll add it in below....

 

[Kaz]

Hi rpw
The category is a mixture of Age, Gender, Sexual
Orientation, Race, Religion and Dependants. The Leaflets
are numbered e.g. A 01,A 02,SO 01, SO 02 etc etc. They are
numbered by my manager. Would I be better putting the
categories A, SO, G etc then the number in a seperate
field?
Do you want to send the reply to my email address, if you
don't mind. it is (e-mail address removed)
Is that enough information. I am ok with creating forms
etc it is just the design and setup I have real problems
getting my head around it.

-----Original Message-----
Hi Kaz,

I'm sorry, but I'm not quite sure I undersatnd what you

are asking for, so I have a few questions for you before I
supply an answer OK?

By "category section" do you mean the category table or

the junction table? Or a form?

Who creates these leaflet codes - you, the publisher? Do

the letters and numbers actually mean something? Are you
wanting to use the "leaflet number" as the primary key ID
or as a descriptive field?

It seems to me (from your post) that it's possible that

you and I are "not on the same page" with ID's and PK's so
I'll add a little clarification.

In the table structure I suggested, I neglected to

identify the primary keys but typically, I listed the PK
first and my intent was that the PK would be an
autonumber. In most cases, the autonumber is never viewed
by the user. In the case of the leaflets, it appeared to
me that the "leaflet number" was some code that the
publisher attached to it and it did not seem that it could
be guaranteed unique over time, so I added "leaflet
number" as a descriptive field, and added a separate ID
(PK) field.