Design dilemma, Use of Autonumber or not (1 Viewer)

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.

If you have a table involving people, you need autoNum. They can't be unique.
Cars are unique, vin.
But child tables, say VIN, accessory,
I would use autoNum to distinguish records. Pretty much every table if it can't be unique.

I must say it's hard to really understand what is going on from the data structures you have provided.

Generally, an autonumber can do no harm.

However, the idea is to add an autonumber to a table, and then use that number as a foreign key in a related table. By using an autonumber key, the relationship is independent of the real data. So let's say you add an autonumber field to a "CAR" table, in addition to the unique "Registration Number". (VIN might be a good idea as RanMan says, but it may not always be available)

so if your system goes on to manage vehicle owners, servicing etc, everything is related together by the "artificial" autonumber ID of the "CAR", rather then the Vehicle Reg No.

With Cars, you generally "really" want to relate details by Vehicle Registration number say. However, if you use an autonumber as the Pk, rather than the Registration No, then it becomes easy to change the registration number to a different one, without causing problems.

Whereas, for example, if a Registration number "PLATE1" was used to manage the relationship between a car and the servicing records, if you were to move the plate to another car, you have potential problems. You want to change the records related to "PLATE1" to reflect the new number. You then need to also change the records relating to the car that now bears "PLATE1".

You avoid this by judicious use of an autonumber, in return for the small overhead of managing an additional index.

Another point is that using registration numbers directly, may give problems because users may enter spacing differently

So if a registration number is "MY 01 REG", you may find it entered with different spacing and/or capitalisation. Capitalisation is generally not a problem with databases, but spacing will be.

The others focused on the Autonumber question, and I don't at all disagree with their advice. You also asked a FE/BE and multi-user environment question. Here is my advice.

If multiple users are going to be accessing the data simultaneously, this is not even a momentary question. Split the database. Here is why...

When EVERYONE opens the same (monolithic) database, they each take out locks on the part of the database they use - including forms, reports, etc. Depending on what is being done, you might get various lock conflict messages.

If everyone has a distinct FE copy and only shares access to the BE, then the number of locks in the BE is what it is, but by splitting, the BE share of locks is vastly reduced.

The number of locks on the FE might or might not change at all - but if everyone has a unique FE, the locks from the FE are also unique and CANNOT enter into lock collisions. I.e. you are reducing the probability of locking conflicts by reducing the exposure of the FE locks via isolation of the FE.

I'd also like to ask a separate question. You said

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

Click to expand...

Is that to be taken as "cannot change design" or "cannot change content" ?

I also see those "data for manager 1" and "data for manager 2" etc. fields. I sincerely hope you were not planning to have them all in the same record. Please look up the topic of "Database Normalization" if you had not thought about this design issue previously.

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.

https:// sites.google.com/site/kuldipmond/images/Screen-Layout.jpg?attredirects=0

(ps just remove the 5 spaces after the // or just use from sites onwards.

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.

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.

Click to expand...

In the Access version of this, you have a prime example of a parent/child form where the parent is the stuff that isn't manager-specific and the child lists the checklist items for each manager. And, in fact, at that point you could see who your user is (i.e is this manager 1 or manager 2. etc) and only show that manager's checklist.

Saying that "it only applies to that one row..." is flat-file thinking. You invite increasingly cumbersome maintenance for every manager who wants to stick his/her finger in that pie. Splitting the table to conform to normalization principles reduces future work no matter HOW many folks want to get involved in an approval process.

The front end is the last thing to work on when building a database. I'm not saying you can't have a vision of what you want it to look like or operate when you first start out, but form design doesn't dictate table set up.

When structuring your tables, you look at the data you want to capture and that's it. Next you work on getting data out of your system (Reports). Once you have output figured out, then you move on to input. Forms are the last step, not the first.

You've come to an Access forum, asking Access developers to assist you with your Access database; you are not going to get anyone here to agree with your plan to move ahead with an improperly set up database. You wouldn't go to a Vegan forum looking for pot roast recipes. If you want to continue on with Access, I advise reading up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and give properly structuring your tables a shot, then post back here what you have.

Lastly, your input idea is nothing to write home about. A good form system could make finding and editing just the data users want so much simpler than your concept. But that's for later days--focus on the tables.

just to add to the last two posts.

With a well designed database, there is a lot of serendipity: Things you didn't realise were possible become very easy to achieve. You (and your users) find unexpected ways to do things.

On the other hand, you need to forget some excel usages. The main thing to forget is using the next or previous row. This just isn't easily achievable in a database. There is no concept of "row order" in this respect. Instead, everything is designed around using the "total" of sets of data.

same discussion going on here

http://www.accessforums.net/showthread.php?t=59863&p=320226#post320226

@mond007 - please read this link

http://www.accessforums.net/showthread.php?t=59863&p=320226#post320226

You have got a lot of good advice here which you should take to heart.

Having looked at your proposed design I see you are going to have a lot of calculated fields. This is generally not a good idea. It is usually better to calculate the fields when you need them and not store the results. That way you get the current values and not some old ones.

Thanks to all,

I understand the differences you have stated access vs Excel. Got it!. flat-file is not the correct thought pattern for access.

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.

It is well to note that all Mgrs will only be looking at a reduced dataset (by Country). 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 (parent/child) - 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, persistence my nature and I am keep to learn the "right way" to do this and I will have a good and post back.

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

mond007 said:

Thanks to all,

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 (parent/child) - 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.

Click to expand...

all of this is a function of careful data analysis - it's an art and a science, but it isn't arbitrary.

it's a matter of considering all the data you need to manage, and placing them in appropriate entities (tables) - and then relating those tables.

Ultimately the aim is to avoid "duplication" of data, and more technically "dependence" of data.

manager updates sounds to me to more in the nature of an audit function.

eg - you have tables for
CARS/VEHICLES
OPERATING UNITS
EMPLOYEES

in simple terms, allocate a vehicle to a UNIT and/or a EMPLOYEE (depending on your requirements.)

if the allocation changes, then it's a matter of deciding how you wish to access old allocations. You may just audit the changes - or you may need a table that stores active allocations and history. Analysis of this ought to guide you to an efficient data structure.

What everyone is saying is that you can do what you like but you need to stick to the basic fundamentals, for example a pot roast must be roast in a pot but you could make it vegan

It is well to note that all Mgrs will only be looking at a reduced dataset (by Country). 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.

Click to expand...

50 rows is still a lot to look at. do managers ever cover more than one country or group of countries, do you have more than one manager per country/group? what happens if a manager is ill/leaves and is covered by someone else? or not covered at all. Only you can answer these questions.

It seems to me that your approach is that as a 'tick boxing' solution, excel does the job as far as all are concerned except for the fact that only one user can update the file at a time. So move to access where you can have multiple users updating to their hearts content. Objective met.

So go with your single table - you have made a small step forward. But you will find Access does things that Excel can't and visa versa and your users will start to discover this.

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

Click to expand...

As people start to use access, and perhaps you are making use of some of the Access only functions, they will ask more and more and at this point you will realise the structure is unwieldy and difficult to maintain - so think ahead about the direction of the business and the systems within that business and how they could be improved. For example

all Mgrs need to cross see everybody elses progress in order to aim at a common goal.

Click to expand...

Do they? if so my guess is they do so by visual inspection. But perhaps they are having a bad day and miss something. Perhaps better that when they login in they are advised of all changes that affect them since the last login - with a link to take them directly to the record. It is a different way of working

The problem is that according to the principles of normalisation you would need a very good reason to split this dataset into two (parent/child)

Click to expand...

True... and that very good reason is that if EVERY MANAGER does the same sort of thing - i.e. approves something - then you split the table according to LINEITEM as parent and MGRAPPROVALS as a child, then make the approvals child table have two keys - the lineitem number and the manager's employee ID or department code or whatever....