Design with multiple tables referencing sequential number

[Lorayn]

Morning -I have been asked to create a DB for a security control room and would appreciate some guidance on the design.I am thinking of a separate table for each activity that needs to be tracked.The challenge is each activity must be reported with a sequential number. So if activity 1 has number 121 then activity 2 must have number 122 etc. This will allow reporting of the various activities to be sequential. I have considered one table for tracking the sequential number (not the auto key as will need to be formatted with yy-####)and looking this up with DMAX and incrementing the number via an update query. I now am confused if my thinking is right and if this is best practise from a design perspective. I am using MS 2010 and the DB will be used in a small set with only one person accessing at a time.I am self taught and although have gained a fair bit of knowledge am realizin that each time I look at a DB there is a lot to learn. Any guidance will be much appreciated😃

[alansidman]

Look at this link on design.

http://r937.com/relational.html

[Lorayn]

Thank you very much for the reply.I shall go thru the attachment carefully and let you know if still have my doubts.Great to a forum!

[Lorayn]

Thank you very much for the reply.I shall go thru the attachment carefully and let you know if still have my doubts.Great to a forum!

[orange]

Try writing a clear description of what is required in simple English. It will help you clarify your thoughts, and it will facilitate communication with others. Be careful to keep what is required separate from how it could be done.

For example:
DMAX is not a WHAT. It may be a possible How.
the sequential number (not the auto key as will need to be formatted with yy-####)???

Relational database works very well with 1 fact 1 field --you can concatenate/manipulate normalized data to fit some display format if and when required.

[ArviLaanemets]

Hi!

So you have some activities which have some sequential activities?!! It will be better when you separate them. P.e. You have some ongoing activities, which have some sequential events. Then you can have a database design like:
tableActivities: ActivitieID (primary key, can be autonumeric), RegisteringTime (when the activity was started, date or date and time), ActivityName, ...
tableEvents: EventID (primary key, can be autonumeric as user never needs to know it), ActivityID (links event with activity), RegisteringTime (date and time, so you can get sequence even when several events for same activity does happen on same day), EventDescription, ...

To get p.e. the ordered list for events of specific activity you can use query
SELECT act.ActivityName, event.EventDescription FROM tableActivities act LEFT OUTER JOIN tableEvents event ON event.ActivityID = act.ActivityID WHERE act.ActivityName = 'YourActivityName' ORDER BY event.RegisteringTime
or (in case you always enter event in right order)
SELECT act.ActivityName, event.EventDescription FROM tableActivities act LEFT OUTER JOIN tableEvents event ON event.ActivityID = act.ActivityID WHERE act.ActivityName = 'YourActivityName' ORDER BY event.EventID

Of course instead of autonumber primary keys you can use calculated secuential ones too, but this doesn't give you any advantages in design - and you get a lot of disantvantages, as you have to write a lot more of code. For any reports, the registering time has more information compared to sequential number, and when needed, you can enumerate events in any report too - there are techniques for this.

[Lorayn]

Point taken. Thank-you for the advise.

[Lorayn]

Thank-you for your reply and help.It has given me direction.Going to work on this��