Help in database design! (1 Viewer)

tabos23 · Jul 15, 2015

Hello to everyone,

I have to design a database with the following data (You can see attached also the data):

DevID DevModel SoftwareInstalled SoftTobeInstalled SoftRemoved
1 Lenovo x62 Microsoft Office Antivirus Skype
2 HP D46 Winamp Skype Win Media
3 Toshiba BT45 Win Media Outlook Antivirus

I am so confused...:banghead:
I will have a table Device( int DeviceID, char DeviceModel, etc.) but what I can do with the Software table?
I have Softwares that can be in many devices but also softwares can be in different categories (Installed, To be installed, Removed).
Could anyone propose me how to design this database?
Shall I create one more table for Software Categories?

Thank you very much for your help in advance.

Vangelis

Ranman256 · Jul 15, 2015

this looks like 2 tables

tPC
----------
DevID, DevModel
321, Lenovo x62

tPCSoftware
---------
DevID, Action, Soft , date
321, "Install", "skype", date
321, "Remove", "Outlook", date

tabos23 · Jul 15, 2015

Ranman256 said:
this looks like 2 tables

tPC
----------
DevID, DevModel
321, Lenovo x62

tPCSoftware
---------
DevID, Action, Soft , date
321, "Install", "skype", date
321, "Remove", "Outlook", date

One table will be the Device table for sure. There must be another one for the software but there are also 3 types of softwares. The softwares that has alreeady been installed, these one that have to be installed and finally the ones that have been removed.

So I agree for the first table:
tPC
----------
DevID, DevModel
321, Lenovo x62

There must be another one I think for Software:

tSoftware
---------
SoftID, SoftwareName
1, Microsoft Office

and I think that there must be also another one for Software category:

tSoftwareCat
------
CatID, Category
1, Installed
2, TobeInstalled etc.

But I don t see how the Software category will be connected with the device...

spikepl · Jul 15, 2015

Since you can have an application on one or more devices, and a device can have one or more applications on it, you need a junction table to register such many-to-many relation. Google junction table.

tabos23 · Jul 15, 2015

spikepl said:
Since you can have an application on one or more devices, and a device can have one or more applications on it, you need a junction table to register such many-to-many relation. Google junction table.

Yes I know that I have to create a junction table in many-to-many relationships. My question has to do with the Software category mostly.

So there will be tDevice, tDeviceSoftware(junction table), tSoftware and tSoftCategory and probably tSoftwareSoftwareCat (junction table) because this is also a many-to-many relationship too.

But this design will give me for example a query where I want to see how many "installed" softwares has each device? I mean how it will be connected the Device with the Software categories?
Is it clear what I mean?
Do I need also a junction table between SoftCategory and Device?

*It s mostly Software status than Software category...

spikepl · Jul 15, 2015

Make the tables and relations, show it with screenshot from Relations window.

But before anything else: think! Your installed/requested etc categories affect a device-software combination, not one of them separately.

tabos23 · Jul 15, 2015

spikepl said:
Make the tables and relations, show it with screenshot from Relations window.

But before anything else: think! Your installed/requested etc categories affect a device-software combination, not one of them separately.

That's how I think of it...But I do not understand how the Software category will be connected to the Devices...

spikepl · Jul 15, 2015

But I do not understand how the Software category will be connected to the Devices.

Neither do I.

Which elements (or entitities or objects) are involved when installing software? And how is that reflected in your diagram (it isn't right now)?

jdraw · Jul 15, 2015

tabos23,

Write a 2 -3 line description for each "thing"- clear enough so you don't confuse one with the other. Then, write the business rules
eg.
There are many Applications
There are many Devices
An Application can run on Many Devices

Research junction table. The arrows in your diagram mean What?

I recommend you work through this tutorial to get a refresher on table design and relationships. You have to work through the tutorial to get the benefit, but you will learn a procedure that you can use with any database design. There is a solution with the tutorial.

Good luck.

tabos23 · Jul 16, 2015

jdraw said:
tabos23,

Write a 2 -3 line description for each "thing"- clear enough so you don't confuse one with the other. Then, write the business rules
eg.
There are many Applications
There are many Devices
An Application can run on Many Devices

Research junction table. The arrows in your diagram mean What?

I recommend you work through this tutorial to get a refresher on table design and relationships. You have to work through the tutorial to get the benefit, but you will learn a procedure that you can use with any database design. There is a solution with the tutorial.

Good luck.

Thanks for your help jdraw!
Well I will explain you all the business rules.
There are many devices
There are many Softwares
There are many Software Categories.

A device can have many softwares,
A software can be in many devices
A software can be in many Software categories(status)
A device can have softwares in many categories(status)
So for example:
- Device 001 can have:

Skype(software_1) has already been installed(category_1),
Office 2000(software_2) to be removed(category_2)

- Device 002 has:

Office 2000(software_2) has already been installed(category_2),
Skype(software_1) to be removed(category_2).

My problem is that a software can be in category 1 for one device, but it can be in another category in another device. Like the example I mentioned before.
Does this make sense?

spikepl · Jul 16, 2015

A software can be in many Software categories(status)

Nope!

Again - which bits are involved when you say "category"? You've actually listed them in your examples.

tabos23 · Jul 16, 2015

spikepl said:
Nope!

Again - which bits are involved when you say "category"? You've actually listed them in your examples.

What do you mean by bits?
I am trying to be as more detailed as I can.
As I mentioned before consider category as Status...It makes more sense.
Status could be:

Already installed
To be installed
Uninstalled

So...Device 1 can have as installed(category1) softwares: Skype and Office
but Device 2 can have Skype and Office as softwares that need to be installed (category2)

I was just thinking now that I can create in Application table, three applications for each one...
So for example:

ApplicationID, ApplicationName
1, Skype_Installed
2, Skype_ToBeInstalled,
3, Skype_Uninstalled,
4, Office_Installed,
5, Office_ToBeInstalled,
6, Office_Uninstalled

I don't like this option to be honest and will be a nightmare to enter the data cause I have 6000 devices and more than 500 Applications...
But I really don t know what else to do...

jdraw · Jul 16, 2015

What exactly is a device?

What are you really recording/doing/adding when a "software is uninstalled"?

You should research "software configuration management" (google) and get some info on what others are doing. It may be more than you need, but you may get some key points to help with your design.

How do you keep such a data base current? You will need automated tools --such as the free Belarc Advisor (I'm not affiliated but do use it from time to time to get an software from my PC). And it really depends on the level of detail and subject matter.

Getting into applications, records, forms, reports, procedure.....with different database software Oracle, MySQL..... written in different languages java, vba, python... can be a life-long challenge.

Figure out WHAT your requirements are (scope the problem), get confirmation, do some analysis, then layout a plan.

tabos23 · Jul 16, 2015

jdraw said:
What exactly is a device?

What are you really recording/doing/adding when a "software is uninstalled"?

You should research "software configuration management" (google) and get some info on what others are doing. It may be more than you need, but you may get some key points to help with your design.

How do you keep such a data base current? You will need automated tools --such as the free Belarc Advisor (I'm not affiliated but do use it from time to time to get an software from my PC). And it really depends on the level of detail and subject matter.

Getting into applications, records, forms, reports, procedure.....with different database software Oracle, MySQL..... written in different languages java, vba, python... can be a life-long challenge.

Figure out WHAT your requirements are (scope the problem), get confirmation, do some analysis, then layout a plan.

jdraw I really appreciate your help!
It s not as complex as you think!
I already have the data in an Excel spreadsheet and I just want to transfer it to Access in order to do analysis easier.
A device can be a laptop or a desktop. The data I have is much bigger than the one I presented before. I just focused on the data that I am struggling to design the database.

Many thanks again!

jdraw · Jul 16, 2015

Did you work through the tutorial I mentioned earlier? It will help you build a normalized database with appropriate relationships to support your analysis.

tabos23 · Jul 16, 2015

jdraw said:
Did you work through the tutorial I mentioned earlier? It will help you build a normalized database with appropriate relationships to support your analysis.

I can't atm cause I am at work but I ll have a look on it for sure.

Since I have to do the analysis asap I ll just create a table for each Software Status and I will connect it with the Device table.
So I will not have Software Table but I will have InstalledSoftwares, ToBeInstalledSoftwares, etc which I will connect them to the Device tbl with a many to many relationship.
It's not the best solution but I think it will be ok for now.

Help in database design! (1 Viewer)

tabos23

Registered User.

Attachments

Ranman256

Well-known member

tabos23

Registered User.

spikepl

Eledittingent Beliped

tabos23

Registered User.

spikepl

Eledittingent Beliped

tabos23

Registered User.

Attachments

spikepl

Eledittingent Beliped

jdraw

Super Moderator

tabos23

Registered User.

spikepl

Eledittingent Beliped

tabos23

Registered User.

jdraw

Super Moderator

tabos23

Registered User.

jdraw

Super Moderator

tabos23

Registered User.

Similar threads

Users who are viewing this thread