My database Design Skills are rusty than a 1970's Lancia , need a nudge :)

[Moby-Dick]

Howdy Folks ,
Its been a good 5 years since I've had to do any real SQL work and just wondering if I coudl get a little helping hand from the kindly folks of HEXUS

I'm trying to put some data together from very quick inventories of a (large) number of laptops.
I already have a table with some base details in it , which isn't too tricky
eg.

Laptop Serial ( primary key)
OS
Location

this has been build up from flat files.
I have a second flat file which holds Laptop Serial + a list of applications installed on that laptop - not a comprehensive list , but picked form those that would have been installed as options from our build process.
file structure is something like

serial,application,version application , version etc. etc.

trying to work out the best way to hold that application data givne that the range of applications is subject to change.
eventually I want to be able to pull reports of total number of a given version of an application deployed.

I had the idea of using something like a bit map to assign a value to each application version , then assign the sum of that line to the laptop ( if that make sence ) but I think its over complicated.
Would it be sensible to try and dynamically create colums in an application table based on those files and have 1 colum per applicaition / version with a TRUE/FALSE type data bit ?
or should I just have a table with Laptop Serial / Application and drop each one in at a line item and let the sorting be done with a few queries ?

any suggestions would be welcome ( btw i'll be using SQl20008 if it helps )

[Flash477]

I would have thought that the following would do you:

tblLaptop (holds latop serial and info)

tblLaptopToApplication (links laptop primary to application primary, and can be expanded to hold per application information, i.e. product keys)

tblApplication (holds a list of applications - general info, so for example Office 2010 Pro would only exist once)

So it would be:

tblLaptop -> tblLaptopToApplication <- tblApplication

[TheAnimus]

Yup with Flash on this, thrid normal form does have its place, this seems like a perfect example of it.

http://www.google.com/url?sa=t&sourc...Cd_XZMVi26Bd3w

if you need to translate the data much to fit in, remember excel makes it very easy to build a list of 5,000 INSERT statements, just mixing strings and then feild from column A, B etc.... quick n dirty

[Moby-Dick]

normalisation rings a bit of a bell I'm doing somethign similar to pull the locality of the laptop from its IP.

Thanks chaps, so I'll need to get hold of a complete list of potential apps / versions to populate tlbApplication ? That should be doable , but ideally want somethign that's pretty light touch when it comes to maintenance. but I'm sure with a bit of head scratching I could come up with a step in the flat file import process to build/append tblapplication for any new deployed version.

[Flash477]

Thanks chaps, so I'll need to get hold of a complete list of potential apps / versions to populate tlbApplication ? That should be doable , but ideally want somethign that's pretty light touch when it comes to maintenance. but I'm sure with a bit of head scratching I could come up with a step in the flat file import process to build/append tblapplication for any new deployed version.

Well you can just add new ones as you go - just means that once its in, all you need to do is create the link.

[Moby-Dick]

That requires a) someone telling me there is a new version , b) me having time to update it
Data on the application is very basic at present so I can live with building the table dynamically ( we literally only capture and are interested in the name and version )

[innovatao]

You mention version of an application, though it's true the standard single linking table of ...

tblLaptop
tblLaptopApplications (linking table)
tblApplications

Would hold true, true normalisation would also occur on the version of the application also...

tblApplications
tblApplicationVersion

As there is no standardisation for versions these days, ranging from version 1 to version 1.2.1.b etc, if would be unwise to create a table of possible versions to link to. Just having tblApplicationVersion linking to tblApplication should be sufficient to quickly drop in a new verison of an application.

However, you will still have to link that version to the laptop, leading tblLaptopApplications to have three fields, Laptop ID, ApplicationID and VersionID.

[th0r0n]

You want these Tables:

tblLaptop (Which contains its ID, Serial Number, OS & Location - what if you get a laptop from another manufacturer that appears to share a serial with another laptop? (Unlikely but still!)
tblApplications (List of all applications)
tblLocations (List of all locations)
tblLaptopApplications (As been mentioned before a table to store Laptops and applications)

And as been mentioned before:

tblApplicationVersions (List of app versions)

[Moby-Dick]

I actually got it working more or less , including some clever stuff of interpretting an IP address into a range and resolving a country form it - I was pretty happy with myself

of course when you do something well , you get rewarded with more "challenges"...

is anyone a bit of a sharepoint ninja ?

need to try and work out how to do a tracking page in order to track the process of certain projects for 50 or so sub groups while only using sharepoint lists as the back end data store.

[Rob_B]

Sharepoint *shudders*

[Moby-Dick]

I know - I feel the same about it, however my employer does not . Sharepoint will be our #1 technology going forward ( we've already got a stupidly large multi million dollar implementation plan in place ) so its one of those things that you got to like or lump

[scaryjim]

If it's a multi-million dollar implementation plan then surely they can afford a few thousand to get a Sharepoint consultant in for a couple of weeks?

[Moby-Dick]

If it's a multi-million dollar implementation plan then surely they can afford a few thousand to get a Sharepoint consultant in for a couple of weeks?

oh they have a big team from MS in on it , but thats for the backbone & core applications. This being a side project of mine has Zero budget as per usual

I'm not asking anyone to do my work for me , just pointing me in the right direction of resources would help