suggestion on correct data structure/database logic

[tomanton]

greetings

i have an excel database of events compounded of 59 columns (crazy). Moving towards SQL database I want to remove most of them if possible, so the question is what's the best way of doing this. So i.e. i have the following columns what would be the correct way of capturing this in a new database. any ideas/suggestions/questions

Forces1

Involvement

control change

Forces2

Involvement

control change

Forces2

Involvement

control change

Forces4

Involvement

control change

Forces5

Involvement

control change

Thanks

[Kyle123]

There's quite a bit missing from your question - a sample workbook would be very useful.

You'd probably start with a table with 3 fields, Forces, Involvement & change control and then insert data as records.

To give you any more than that we need to know much more about hat you're trying to do

[tomanton]

Hi Kyle,

that's actually where I'm at the moment myself, lacking of information. The aim is to move from excel onto SQL/in-house database, and therefore logical structure and design needs to be created, where users could enter data logically and its visible to them too, the form should not be overloaded with too mnay fields but captures all the required information. If that makes sense.
Im including a sample spreadsheet for you, any help would be highly appreciated.

[Kyle123]

From what I can see, you'd need at least the following tables:

Table Event (or whatever it is)

Please Login or Register  to view this content.

Table EventForces

Please Login or Register  to view this content.

You'd also need a table for EventWeaponry, but I couldn't gather what this should look like from your workbook.

You'll also probably want numerous lookup tables. Possibly as a start point:

Please Login or Register  to view this content.

Possibly one for Forces, but that will depend on how much they vary and requirements. For example, do you have lists of forces and have to report on which events they were involved in? If you then there should probably be a Forces table. It's also generally not a good idea to compound different bits of data into a single field (unless they are the same thing) so Iraqi army and Iraqi Army Aviation should possibly be different records in the EventForces table.

It's hard to be any more specific since I don't really understand the relationships between the component parts. Have you worked with relational databases before? If not, start by getting your head around normalization (see here: http://forums.aspfree.com/microsoft-...es-208217.html). Once you've done that get the tables right. Completely forget about user interface until you have the data structure nailed down - it should be the last thing you do, never, ever be tempted to start with it - that way leads to ruin

[tomanton]

Hi Kyle,

Thanks for your response. you pretty much got it right. For the forces Iraqi army and Iraqi Army Aviation would come under Iraqi forces (forces1), coalition would become forces2 and so on, but there is no list set of what can be under each force so its editors discretion to capture all the forces correctly, a dependent on the forces list would be ideal but not possible as its ever-changing, so I'm not sure whether forces would need a separate table probably yes:

forces_Id
Event_id
force (Iraqi forces)
force description (Iraqi army)
involvement (yes/no) but that's probably necessary as the entry itself indicates involvement
control change (+1 = gain / -1 = loss)

for weaponry I think this would be the table:
id
eventweaponry type (controlled list)
exact_model_known (yes/no)
weaponry_id (taken from another table where all the weaponry is listed)

lookup tables will be required as you said for
accuracy
magnitude
source type
reliability

I think I can now create a data model in powerpivot. so once that's done, what goes next? creating user interface?
This is a very good exercise for me and your help is highly appreciated, i read commended article and I'm prepared to finding a good solution in here

[tomanton]

Hi Kyle,

thats what i end up withdata model.PNG

[Kyle123]

Some questions/pointers:

Firstly, never use spaces in field names. It's gonna cause you issues.

You are missing the other tables, but I guess you're aware of that.

I'm confused by your forces table, why do you have an ID and Forces ID? - something seems wrong here. (the same for the weaponry table)

I'm confused, I'll admit I don't use it, but I don't think Power Pivot is a database

[tomanton]

will get rid of spaces, thanks
for Id's i assumed they would work as PK for those tables, is it not how it works?

powerpivot is used here for the conceptual data model representation, as i believe i will need to write all the constraints and data types later against each other

[Kyle123]

Ok, I think I understand. WeaponId & ForcesID should be called EventId so it's obvious what they refer to

The id fields should probably all be auto numbers

[tomanton]

Thanks for helping better understand the concept of data modeling.

so apart from adding additional information against each entity what next steps would be for this? connect other tables to make a holistic view ?

[Kyle123]

Yes, get all the tables in your model first.

Once you've done that, you'll probably start to find you need additional fields that you haven't thought about yet. For example an active toggle for whether an Event is active or not, or a deleted flag (you wouldn't normally delete from a database, rather flag the record as deleted).

[tomanton]

also, how i add constraints and rules for each of the items? and how do you draw a picture of the tables?