Hi everyone,
I have an excel sheet with 5000 rows which I want to export into MS Access as I realised it was becoming unmanageable and wanted a better data storage option.
But as a complete beginner, I’m struggling to visualise how I would structure this database, in terms of data modelling. The data consists of:
- a list of world destinations with a science theme and includes several ‘categories’, e.g. accommodation / natural world / science centers / historical places.
- all the records have SOME common fields, such as country, address, GPS coordinates, description, contact name, web url etc
- but each category also has fields which don’t apply to others – e.g. only the accommodation category will have sub-categories such as ‘hotel’, ‘B&B’ and ‘camping’ and often ‘room capacity’.
- each country will have many records across all categories – I will be giving my team individual countries to manage/keep updated - or even split into regions for larger countries, but I haven’t looked at forms yet.
Can anyone advise, please? I can obviously adapt the excel sheet accordingly. So….
- Do I stick to one table with lots of empty fields?
- Should I have a table per category (or even more tables) - and which other benefits would that give, apart from fewer empty fields?
- I can't see many relational aspects i.e. ALL records are simply ‘science destinations’ at their basic level. Apart from Contact Names below:
Contact Names - this is the only part I think I can figure out:
- Each contact name may cover more than one destination, but this will only apply to about 1 or 2% of the records – most records will have unique contact names.
- Some records may have multiple contact names
- Some records will have NO contact name, but I can input N/A or TBC.
So I’d need a separate table for Contact Names, despite the vast majority being unique, right?
Otherwise, I can’t get my head around the rest of it.
Finally, this data is for loading into a web directory, Directories Pro. My concern is that having multiple tables will make that process difficult or impossible. I appreciate this is not specifically applicable to this forum, but if anyone CAN offer any help or advice, I’d be so grateful. This is a one-off project only, so I’m trying to avoid cluttering my poor brain with knowledge I don’t yet need. I’m happy to learn as I go once I’ve got a starting point.
Many thanks for any guidance.
Zerameth