Design dilemma, Use of Autonumber or not

[mond007]

Hi
I have been tasked to design and maintain a
database of car information. I in simple reduced form have the following dataset
:

Imported Country DataTxt Field 1
Imported Static DataTxt Field 2
Imported Static DataTxt Field 3
…
Imported Static
DataTxt Field 15
Autonumber
Common Enterable data 1
Common Enterable data 2
…
Common Enterable
data 8
Data
Related to Mgr 1
Data Related to Mgr 1
Data Related to Mgr 1
Data Related to Mgr 1
Data Related to Mgr 2


Data Related to Mgr 2
Data Related to Mgr 2
Data Related to Mgr 3
Data Related to Mgr 3
Data Related to Mgr 3
Data Related to Mgr 3
Calculated Field 1
Calculated Field 2
Calculated Field 3
Calculated Field 4
Calculated Field 5
Calculated Field 6
RAG Status
Text
Field
Comments

Basically, the first 15 fields are imported data from
another database and UNMODIFYABLE - subsequent fields are data that needs to be
added by various different Mgrs.

a) It is my intention to have a SPLIT
form with the top half to add/modify the data all for fields after the 15th
field and the bottom half in read only mode only for viewing and selecting the
row to maintain.

b) There needs to be no need to add new record per se
manually, but the main feed will need to come from an import form another data
and will ONLY be for the first 15 fields.

c) There also needs to be a
way of filtering the data by country based on the first field. I am tempted to
place buttons on the top form the form which filter the data by a drop down etc.

d) I also need to be able to open this database up to multiple users. No
security needed.

This is going to be my first transition to ACCESS DB
after decades of being a VBA and .Net programming, so I am use to macros and
scripting.

My main hangup is the overriding question of whether I need
an "AutoNumber" field ? To date it feels like I do in order to uniquely identify
the car derivatives. BUT I am then unsure how to import data (both initially or
incremental) and then allow ACCESS to do the autonumber part of it.

I assume i would have an update query which is sitting on the external data DB
which inserts rows into my DB ?

My final consideration is whether this
needs to be a split database i.e. front and and backend detached so that it can
be truly multi-user ?

Any assistance would be greatfully appreciated.

Thanks in advance.

[CJ_London]

your design does not look normalised and you need a good reason to be using calculated fields in a table (access is not excel)

Your descriptions are too vague to provide a more focussed response, but concentrate on getting your table structure right first before thinking about forms and reports.

And autonumbers - yes, with a few small exceptions you need them for all tables.

allow ACCESS to do the autonumber part of it

access will populate this automatically - it is required to be able to uniquely identify a record. You may have another field or combination of fields which also uniquely identifies a record but you have to ask yourself - what if I mistyped something and corrected it later.

Basically, the first 15 fields are imported data from another database and UNMODIFYABLE

File, so these should be in a separate table

Data related to manager would probably be in one table for all managers

[ButtonMoon]

Your database design should not be based on whether data is modifyable or by whom. Business rules are the basis of database design, which means you need to understand your data properly and be guided by good design principles such as Normalization. What we can guess from your list of fields is that you have repeating sets of columns in a table, which is generally an anti-pattern you should avoid.


Your question about autonumbers is the wrong question. An autonumber is just a technical feature for generating a sequence of numbers. Your data needs identifiers but incrementing numbers don't necessarily make good identifiers. What really matters are the keys of your data and you need to determine those in your data by understanding what the data means and how it will be used. Again, normalization is a form of analysis to guide what keys should implemented in your database. When you've understood and identified the dependencies and keys then you'll have a better idea of whether and how autonumber columns might be useful to you.

Hope this helps.

My final consideration is whether this needs to be a split database i.e. front and and backend detached

Yes. Always.

[mond007]

Hi

Ok, having heard all the compelling ways forward which I whole heartedly appreciate. I think I have to add some flesh to my original question as posted.

I agree with the split DB as mentioned and Autonumber scenarios. Also the no of columns will not change, nor will the "contents" of the first 15 Columns as this is raw data import from an external DB.

Just some background this was previously a Excel solution and everybody was happy with one row per record and was used succinctly as a tracker for recording progress though all the Mgr actions). The "ONLY MASSIVE" drawback was that many users made this difficult to access owing to locking of the file etc.... age old problem with Excel. It is this and this only that I am looking to overcome.
The Orange field is just different blocks of data. i.e. Europe, India China etc. All columns after column P are for tracking purposes.

Let me first say that I am aware of all the "Database normalization" concept but there are some instances when maybe the way I am contemplating could be acceptable. Here is my thoughts :

All data related for the Mgrs to input are always related to the one and only row that is being maintained. There isn't one-to-many relationship. Each person will update various different stages of the input and checklist of things. So when anyone updates various things like 'Doc Signed off" or "Checks Done" etc it will always be answers related to that row ONLY.
The calculated field for wanting of a different expression are just things like "No of days between" certain check dates entered or a RAG status which is calculated by the no of "Yes" answers to above questions. Quite simply a Green simply means all inputs are in. I feel there is no need to the base data to be in one table and the Mgr inputs to be another as they all related to the progress of the same of data.
(i.e. there is no Reg No per se).
Maybe I've spent too long working in Excel lol.

Basically, I have added a couple of images which depict my intentions.





Hope all can see the images.

Maybe I need to design the Back end different to the way I vision the front-end.

Let me know what you think.
Again this insight is invaluable to me and very much appreciated.

[CJ_London]

Main thing to consider is that Access is not a larger version of excel. It just does not work the same way. Simply put Excel data is stored and displayed short and wide, Access is stored tall and thin and forms and reports are used for display and can be pretty much whatever you like. Excel has millions of columns which you can scroll left to right. Access forms are limited to 22 inches of width.

So you are saying that

manager 1 always has 3 tests and overwrites the date column as each test is completes
manager 2 always has 3 drawings and a couple of input docs
manager 3 always has 2 docs to complete

also each manager needs to see what the other managers have done/have left to do

each manager needs to scroll/filter through all rows to find the row they want

etc

[ssanfu]

This is going to be my first transition to ACCESS DB

Here are some suggestions:
Use only letters and numbers for object names (exception is the underscore). NO spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
---
Be aware of reserved words. "Data", "Text" and "Field" are reserved words and shouldn't be used as object names.
List of reserved words: http://allenbrowne.com/AppIssueBadWord.html
---

See:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...=1#post1488210

[mond007]

Ajax,
The things you mention are correct.

manager 1 always has 3 tests and overwrites the date column as each test is completes
Yes.
manager 2 always has 3 drawings and a couple of input docs
Yes.
manager 3 always has 2 docs to complete
Yes

I understand the differences you have stated access vs Excel.
The problem is that i work in a world where Excel is dominant/overused and has a culture of being the accepted solution to everything and myself being the IT/Dashboard Solution provider will have to nursemaid them all into a new world of thinking.

To answer your other query about seeing/scrolling through loads of data is not quite right. All Mgrs will only be looking at a reduced no of rows. I will be providing a filter by Country hence reducing/filtering to a max of 50 rows. Not to mention there is a search box in access to. Also all Mgrs need to cross see everybody elses progress in order to aim at a common goal.

I can easily fit all the columns strategically placed in to 22" of width so that is not the issue.

I guess I am a little perplexed on how to normalise this dataset. Perhaps I should keep columns A to P and any other core data in a main "Vehicle" table and then a "Mgr Updates" Table for all the response entry.
The problem is that according to the principles of normalisation you would need a very good reason to split this dataset into two - this reason need would be a one-to-may relationship. The problem is that there isn't a one-to-many relationships in order to justify a separate "Mgr Updates table" per se.

However, ppersistence my nature and I am keep to learn the "right way" to do this and look forward to a suggested split.

Thanks in advance.
ps duplicate post but I thought I would get a different take from two places. sorry.

[CJ_London]

You need to go with a design that works for you. Normalisation is a principle - do it right and many things become easier. Think about what the data is used for, rather than what it is going to look like on the screen. The principles have already been outlined so you need to apply them. You have not described your business in anything like the level of detail required for me to provide detailed advice (e.g. only one manager1 or are there several, communication with other managers, type of reporting to be done). I'm happy to point you in the right direction, but not do your job for you.

this reason need would be a one-to-may relationship

in the context of one file, to be completed in the same way as excel, perhaps, but using field descriptions like 'test1', 'test2' implies otherwise. Don't forget, your excel spreadsheet works because that is the way excel works, it does not mean it is the best way of doing things.

With regards splitting the db, this is a must. All tables are stored in the backend which is located in a mutually accessible location and all users have their own copy of the front end on their machines. Front ends contain everything except tables.