Database Design help for a Begginner

[dino2dy]

Hello all I have a question related to a design problem I have

(not sure how important this is to the question but I use Jdeveloper 11g 11.1.1.2.0 for database development and also application development, using adf web fusion)

I have a table called Stores that represents physical stores. This table is connected to another table called Technical Equipment in a 1 (Tech Eq. Stored) to many (Stores) relationship.

These stores however can contain different Casino Gaming Machines, which have different designers and need to be a different table. So one type of a gaming machine can be in many stores and a store can have many gaming machines of the same type. I dealt with this initially by creating an intersecting table with their primary keys.

Sadly these stores can also contain computer terminals that are used for sport betting, or dog racing activities. These can also be from different manufacturers and so I decided to put them in a different table. Again the same applied as to the gaming machine so another many to many with the Tech Eq. table so again I did the intersecting table.

Unfortunately the Tech.Eq table also has ANOTHER many to many relationship. This time with the type of internet connection used. Different stores have different connections but a store can have more than one type of an internet connection (broadband and dial up). Also again with different providers I created a third table called Internet connections and again used an intersecting table to settle it.

Problem is that now the Tech Eq table is used in 3 intersection tables and it seems a very crude solution.

Keep in mind that the Type_of_Gaming_Machine and Type_Of_Internet_Terminal tables have just 2 attributes, the id which is an auto sequencing number and the name.

So now I am looking to you to help me find a better solution. If you too think this is the best way to do it could you suggest a way to implement it into a form because I have no idea how to create CRUD capable forms for all these tables. I know I am supposed to use Many to many shuffle but not sure how since I have so many intersection tables all related to the same table.

Any help you guys can offer would be appreciated

(I use differing but similar names for tables, but the key thing is there are 5 tables: Stores, Tech_Eq_In_Stores, Type_Of_Gaming_Machine, Type_Of_Terminal, and Type_Of_Internet_Connection)

[MoZo1]

a store can have many gaming machines of the same type

Then it's not strictly a many-to-many relationship, but a class-instance: Every gaming machine has a type and a container.
Here is an example for many-to-many: http://en.wikipedia.org/wiki/Many-to-many_(data_model)
Notice that if you take an instance of a book, it inherits it's author and title, but for your gaming machines, it only inherits it's type, and you have an owner per instance.
My point is that the records in your "intersecting table" refers to physical objects (machines you can touch), while in a strict many-to-many they do not.

Different stores have different connections but a store can have more than one type of an internet connection (broadband and dial up). Also again with different providers I created a third table called Internet connections and again used an intersecting table to settle it.

Only right if there are only predefined types of connections, else it's many-to-one:
Normally you just have a connections table including the foreign key.
But if you see a type or a provider here, and you have any info about the other (or with other words, you could insert a type-provider pair that is invalid), then yes, it's an "anomaly", and you have to solve it like this.

the key thing is there are 5 tables

More tables doesn't mean worse or complicated solution. There are databases around with 200+ tables, and parameterized queries longer than a page on my 1280*1024 resolution. You just separate a model section in your program, and use the query only through that.

[dino2dy]

First of all I wanna say thanks for your reply, I was starting to think no one would ever reply. Now as to your points:

Then it's not strictly a many-to-many relationship, but a class-instance:

I am not sure what you mean when you say class-instance so could you maybe elaborate???

My point is that the records in your "intersecting table" refers to physical objects (machines you can touch), while in a strict many-to-many they do not.

Again not sure why referring to physical objects excludes it from being a many to many table so if you could help me understand I would be grateful. My intersecting table is just that, it contains 2 foreign key attributes (for Tech Equipment and Type of machine). Thing is both of the tables that are connected via the intersection table can have different outcomes. There are a few types of machines and there is every chance that in the future a new one might be added to the list. Same goes for Tech Equipment, as each object that has a different setup is a new row in that table.

[tesuji]

Hello

Greetings from the hood!

Assuming you want to manage individual data for every occurrence and type of gaming machine you have installed on variuos stores you can do that by a one-to-many relationship between stores (table store) and indivudual data (table machinedata) and a many-to-many relationship between individual data and type of gaming machine (table machinetype).

store -----< machinedata >-----< machinetype

Sorry, the rest contains an mistake, therefore

**** deleted ****

I will answer you asap if that mistake is cleared.

Btw, it is a good idea to draw an entity relationship model of such data model, visual paradigm or even workbench of mysql are nice tools. With ERM one can see much more than only reading from written specifications.

-- tesu

[dino2dy]

Thx for your reply

I have drawn a visual model (you can do it in Jdeveloper) but didn't help me much :).

I am not sure what the table machine data represents in your example??? A store can have many types of gaming machines and one type of a gaming machine can be in many stores so the only way I see to do this is a many to many. I could have done it directly with stores but since I have other technical equipment I connect it to the Equipment table which holds everything about the technical equipment of a store and then connect that to the store so

store -----< tech_equipment >-----< machinetype (although the many to many is done using an intersection table)

[tesuji]

Hi

sorry, i have overlooked this nice oracle tool. I have some idea for a solution how to manage your heterogen data based on the little model I gave. Principle Idea: handle all your equipment like in a retail system, customers are your stores. Game machines, internet connections are the material a la SAP R/3 etc.

I'll drawn a ERM in principle for you, now I am off, will be back in the late afternoon.

-- tesu

[MoZo1]

I am not sure what you mean when you say class-instance so could you maybe elaborate???

It's more or less the same what you use in OO programming.

Again not sure why referring to physical objects excludes it from being a many to many table so if you could help me understand I would be grateful. My intersecting table is just that, it contains 2 foreign key attributes (for Tech Equipment and Type of machine). Thing is both of the tables that are connected via the intersection table can have different outcomes. There are a few types of machines and there is every chance that in the future a new one might be added to the list. Same goes for Tech Equipment, as each object that has a different setup is a new row in that table.

First the matter when you design is not the actual data, but any combination of data that is considered valid. Second, here is a half sentence of yours I've noticed: "and a store can have many gaming machines of the same type"
So many-to-many is just a term, it depends on how you define. Strictly speaking SQL can't do many-to-many, so you use a two side many-to-one and one-to-many combining table. That table doesn't refer to physical touchable objects, so when you do a physical data model, the two objects you have will have many-to-many relationships, like book and author.
But if that middle table happens to refer to physical objects (and you can't ignore them in your model to get a longer coffee break instead), like when you have different machines, and each can have only one type and store, then the subject is changing, not the relationship. Now you have three objects: machine types, machine instances, and stores. One store can still have more than one type of machines, and one type of machines can still be in more than one store, but now they are not directly connected on your physical model. The improvement is that now you can have two machines of the same type in the same store. Depending on if your half sentence has been interpreted correctly, this is necessary. (If not, then considering the idea and dropping it, while writing at least two pages of documentation is necessary. :D)
A shortcut would be to keep it simple many-to-many to add a field which contains the number of machines of the same type in the same store (as I call already GROUPed form), and that can be done in practice, until you have to refer to them uniquely. So for example if you have to keep track of which machine need repairs...

Hope this helped.

[tesuji]

Hello

Now I have put my idea into an ERD. It is based on those two requirements:

(1) Each physically existing equipment must be associate to the store where it is located.

(2) There are various sorts of equipments where every sort is described by its own set of properties.

First requirement is represented by table storeequipement which has its own primary key although it seems to be a true many-to-many relationship between store and equipment. However, for there are stores having multiple equipments in the same line the pair (storeID, equipmentID) occurs repeatedly. One could add this pair to primary key too, what will allow faster table joining.

Second requirement is given by tables equipment and specificequipmentdata. Equipment holds all common data of equipments varieties. Specialized data of those varieties are hold in table specificequipmentdata. This is some kind of isa-relationship of generalization/ specialization from OO as MoZo1 already noted. Eventually, foreign key equipmenttypID should be added to specificdatatype for clearer grouping.

In my prior posting was a bad mistake, I couldn´realize the isa approach correctly. I have also chosen some more universally valid terms and names to develop the ERD.

Well, that´s it.

-- tesu

[MoZo1]

Your field value in the table specificequipmentdata contains data from different domains. Like it can contain color, weight, power requirement... So it's inefficient to for example sum the power requirement of stores, or the weight of machines per store for moving, etc..
Well, from one aspect, you measure values, and you just have to be able to store them, whatever you've been asked to measure. They are the same to you: Data that an instrument writes out: every measurement is an object of the same kind to you. From another aspect, those are data about a specific property of an physical object you've measured them on.
I don't know which normal form it violates if it does at all. In practice, either there are very few types of data, and it's changing very slowly, like in every year, so I can make it faster with nullable columns, and some very rare runtime DDL (bad practice!), or there are large variety of data types, and this structure is perfectly reasonable. I would really like to know the theory behind this... Any help? :D

[dino2dy]

First of all I wanna thank you guys for all the replies. I am truly feeling the love.

Secondly I have a diagram that shows the full ERD of what I did but I dunno how to attach it here and Its in my native language (Bosnian) so dunno how much that would help but I am willing to try it if u guys could just tell me how to attach it (like you did Tesu)

As for your theory question... Well there are a loooot of stores, all around my country. The table tech.equipments just represents an instance of all the technical equipment inside a store. If 2 stores have identical equipment (1 printer, 2 gaming machines etc) then they have the same tech_equipment_Id. If not we create a new Tech_equipment row in the table and add it to the store. Equipment can be added or taken from a store but how often or rarely this happens varies greatly.

I also have a table Equipment which is for mechanical equipment if you will tables and such, so not really sure if the weight measurments are a concern. The power requirements are a good idea though.

Once again thanks for all your guys help.

[dino2dy]

Ok this is the diagram.

Not sure if its the latest one but nothing major happened maybe some field changes inside tables.

The problem position is in the lower right corner. You can see the teh_equipment table (Teh.Opr) as well as the connecting tables (Adsl), Tip_Masine (type of gaming machine) and Tip_Terminala (type of terminal)

[dino2dy]

Wow the level of activity on the thread went down considerably since I posted the pic, not a good sign

[tesuji]

>>> MoZo1
The values are stored as varchars. This is quite common. I only make use of two kernel SQL features which allow (1) that values be stored as strings independingly of their individual datatypes and (2) that casting by way of kernel function cast() be possible to convert from all standard datatypes (except of BLOP etc) into varchar and vice versa (which is not possible in to-day mysql due to nasty bugs still pending since 2007). Casting between datatypes does not violate any normalization rule, as far as I know.

>>> dino2dy
I don' think that everybody in here is fallen deep sleep. Well, your posted diagram is useless for one cannot use it to understanding your concept, for formally checking dependencies/relationships nor can't be basis for discussion. This is not a matter of language but solely of low resolution of screen shot.

I have already introduced a functional concept in principle on how to manage heterogen properties of general objects what hasn't been sketched in passing. By contrast, it's widely used for such issues, for example in implementing DIN4000. So far I don't know whether it might be useful for your sort of problems nor if you to design a new solution or be bound to already existing structures.

-- tesu

[MoZo1]

Thanks, I've found it! It's called EAV model:
Here is a little teaser: http://pratchev.blogspot.com/2010/07/refactoring-entity-attribute-value.html
And here is a little brighter objective (but much longer) description: http://en.wikipedia.org/wiki/Entity-attribute-value_model
As I understand, EAV doesn't violate any NF because it's not a relational design.

[tesuji]

Thanks, I've found it! It's called EAV model:
Here is a little teaser: http://pratchev.blogspot.com/2010/07/refactoring-entity-attribute-value.html
And here is a little brighter objective (but much longer) description: http://en.wikipedia.org/wiki/Entity-attribute-value_model
As I understand, EAV doesn't violate any NF because it's not a relational design.

Hello MoZo1

Thanks a lot. To date, I haven't known that this concept would really have an official name, Entity-Attribute-Value model, EAV. And it is indeed frequently used in that odd MUMPS language. Actually, mid of the 1990th I introduced it when I was designing the database system for the Hewlett Packard CAD systems.

-- tesu