Mysql Table Schema

adorearun · #1 (**permalink**) 06-19-07, 02:02

Hi All,
I have a question.
For me i have an attribute for different type say type1,type2 .....so i have same attribute with different attribute id for different type say 1 for type 1 and 2 for type 2.My question is

Whether i can create six tables for six types and insert data based on their attribute id

or
I can create a single table for an attribute which will have all the six types values in it

say for example
Type 1 has
resourceid,attributeid,minvalue,maxvalue,total,tot alcount as columns .similarly all six tables have the same column .suppose if i want to get the data for all types in a sorted manner like minvalue asc then i have to query the all six tables .Instead if i have only one table for a single attribute i can query the single table and get the data and one more inportant thing is these tables grow dynamically.
so can you suggest which way i can create my schema.
Kindly let me know if you have any doubts and sorry for confusing you

Thanx n advance,
Arun

aschk · #2 (**permalink**) 06-19-07, 04:43

Do some reading on database normalisation.
What happens when you have 20 types? Do you want to manage 20 tables? How do you propose choosing which table to utilise based on your types?

What is a "type" exactly? It is very non-descript. Type of what? What is the relationship between the type and the table you have represented in your post?
i.e. does 1 type have lots of records in that table?
type1 has (resourceid,attributeid,minvalue,maxvalue,total,to t alcount)
type1 has (resourceid,attributeid,minvalue,maxvalue,total,to t alcount)
type1 has (resourceid,attributeid,minvalue,maxvalue,total,to t alcount)
type1 has (resourceid,attributeid,minvalue,maxvalue,total,to t alcount)

or just one
type1 has (resourceid,attributeid,minvalue,maxvalue,total,to t alcount) ?

adorearun · #3 (**permalink**) 06-19-07, 04:57

hi,
sorry for confused post......may be thinking of this schema lead to that

..
Actaully the type is resource like servers linux,windows and attributes are like cpu, memory etc and each attribute has its own id for its type meaning the cpu attribute id for linux is 7 and for windows is 8 similarly i have it for aix,sun etc....
If i create a table for different types like linux table,windows table etc ....and if i want get the CPU data of all types i need to join all say six(different types of servers) tables ...
OR
If i create a single table for CPU and insert the data for all types in this table and if i want to get the CPU data i can query a single table .....
so which way i can do it.am i clear ?
Also the rows in the tables grows ..........how it be act when it comes to scalablity .......we have attribute id as primary key ....because it is unique for its types.

Thanks,
Arun

aschk · #4 (**permalink**) 06-19-07, 08:11

It seems clear that you need to separate your specification out properly.

You have MANY machines.

You have a few operating systems (linux,windows,aix,sun).

Each machine can ONLY have 1 operating system (i'm not counting dual-boot).

Therefore you have a one-to-many relationship.

Each machine can have 1 cpu ONLY (i'm not counting dual cpu machines).

Each cpu has a name.

One cpu can occur in many different machines. i.e. machine 1 & 2 can have an Athlon XP.

This requires three tables as follows:

Code:

CREATE TABLE machines(
  machine_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  cpu_id INT UNSIGNED NOT NULL,
  ram INT UNSIGNED NOT NULL,
  hard_drive_size INT UNSIGNED NOT NULL,
  operating_system_id INT UNSIGNED NOT NULL
) ENGINE=MyISAM;

CREATE TABLE cpus(
  cpu_id INT UNSIGNED NOT NULL PRIMARY KEY,
  name VARCHAR(255)
) ENGINE=MyISAM;

CREATE TABLE operating_systems(
  operating_system_id INT UNSIGNED NOT NULL,
  operating_system_name VARCHAR(255)
) ENGINE=MyISAM;

Your operating system table will look similar to the following

Code:

ID | Name
=============
1  | Linux
2  | Windows XP
3  | AIX
4  | Sun

Your cpu table will look like this:

Code:

ID | name
==================
1  | Athlon XP 2500
2  | Athlon XP 3200
3  | Pentium III 1200Ghz
4  | Pentium 4 2.5Ghz

And thus your machine table

Code:

machine_id | cpu_id | ram  | hard_drive_size | operating_system_id
==================================================
1          | 1      | 1024 | 40              | 2
2          | 1      | 512  | 60              | 1
3          | 1      | 512  | 80              | 3
4          | 2      | 1024 | 60              | 2
5          | 2      | 512  | 80              | 1

This is just an example and simplified version of what I "think" you need.

adorearun · #5 (**permalink**) 06-20-07, 01:44

hi,
thanks for the reply and i have another question say if i have two tables like t1,t2 have identical columns like id, name, value ....if i want to get value for the particular id say 1 and 2 where 1 present in t1 and 2 in t2.How do i get the data from one query ...because there is no any relation between the tables.any help is appreciated.

Thanks,
Arun

r937 · #6 (**permalink**) 06-20-07, 05:45

if you want the value "for the particular id say 1 and 2 where 1 present in t1 and 2 in t2" then you don't have to run a query at all, you already know the values!

in general, you would use a UNION query, but what you select from each table has to be union compatible (do a search on that topic to see what it means)

aschk · #7 (**permalink**) 06-20-07, 05:56

If you have identical columns in two tables it makes me think that you should be using one table to conglomerate them, especially if they contain the same types of column and represent the same data.

adorearun · #8 (**permalink**) 06-20-07, 06:54

thank you very much for your answers it helps me lot

mike_bike_kite · #9 (**permalink**) 06-20-07, 08:09

You definitely want to use just one table. Because everything's in one table all the code is simple to write and maintain.

Code:

create table lookup(
   field_name    varchar(50),
   valid_value    varchar(255)
)

insert lookup values ( "OS", "Linux" );
insert lookup values ( "OS", "Windows XP" );
insert lookup values ( "CPU", "Athlon XP 3200" );
insert lookup values ( "CPU", "Pentium III 1200Ghz" );
-- etc etc

Imagine if you go with the idea of separate lookup tables for each param - you'll need to create a new table each time you want to add a new parameter, then you'd need to write code to check against this new table. It's a maintenance nightmare!

With one table you could easily have a maintenance screen to add new params or options to existing param. You'd just need one proc to validate a field and you'd never need to alter that code. You could also have one proc to generate the html menu for a given param and again it wouldn't need to change.

Before the flames start I do know the rules of normalisation but the aim of these rules is to produce databases that are easy to maintain. If you just follow the rules blindly without keeping the end goal in site then you end up with a mess.

Mike

aschk · #10 (**permalink**) 06-21-07, 04:24

Start the flame :

Normalisation is NEVER mess if you get it right. Unnormalised data as you have suggested IS a mess and will cause large inconsistencies in your data as time progresses. Imagine if you add two CPUs to a machine that can't have two CPUs..

The point is that you achieve your end goal using methods that are proven to be effective, i.e. normalisation.

mike_bike_kite · #11 (**permalink**) 06-21-07, 05:20

Quote:

Start the flame :

Bring it on - I'm having a quiet day

though perhaps this discussion belongs in it's own thread.

Quote:

Normalisation is NEVER mess if you get it right.

Normalisation is always a good start but it has it's limits. In this case normalisation dictates you should have a new table for each new param on each new type. If the system grows to cover all the different types of object these people may look after (networks, storage, databases, PC's, printers etc etc) then imagine how many new params you'll be needing. Now imagine having to build a new table each time and updating your code to be able to use these new tables. To make matters worse imagine doing this in a banking environment where you need to schedule any change to the database and fully test it before releasing - you'd never keep up. That sounds like a mess to me!

Quote:

Unnormalised data as you have suggested IS a mess and will cause large inconsistencies in your data as time progresses

If all the lookup data is accessible in one simple table you can easily provide a maintenance tool to look after the values being held. If the data is spread across 50 tables then nobody is going to look after the data and it will soon become out of date. It will get worse once the original database designer moves on to another job and the maintenance guys take over - soon your users will be picking windows XP when they really want to pick Vista but that option isn't there yet as no-one has the slightest clue which table to update.

Quote:

Imagine if you add two CPUs to a machine that can't have two CPUs

I don't see any of the proposed solutions detecting this but even if you did spend the time to code that server X can have up to 8 processors while server B can only have 4 - how long do you think it will be before all this data becomes out of date? or were you looking for a job for life here. Surely it would be better to allow the user to simply pick the number of servers from the pull down and ensure that the pull down has a list of reasonable values sourced from the lookup table.

Quote:

The point is that you achieve your end goal using methods that are proven to be effective, i.e. normalisation

Normalisation works great but it does have it's limits. I do speak from experience in this case as I produced a large scale configuration management system for a bank (with lookups!) where we were handling servers, networks, printers, buildings, software, people, departments, projects etc etc. I started of with separate tables for everything and soon realised that this would never work.

Mike

r937 · #12 (**permalink**) 06-21-07, 05:37

Quote:

Originally Posted by mike_bike_kite

If all the lookup data is accessible in one simple table you can easily provide a maintenance tool to look after the values being held.

that's beginning to sound a lot like the dreaded, evil "one true lookup table" (OTLT)

mike_bike_kite · #13 (**permalink**) 06-21-07, 06:25

r937, aschk

There are obviously pro's and cons for both sides of the OTLT and EAV argument. The links are very interesting and full of interesting points but they do seem to cover the worst points of these methods while ignoring the very real problems of millions of lookup tables and databases where the type of data being held changes continually.

The configuration management database mentioned previously would of been impossible to implement without an EAV approach. So by default I guess that makes it the best method to use in this case.

With OTLT I think it's a question of approach - with multiple lookups and foreign key checks you get the data test at the lowest level but get database errors thrown up when the value is wrong. With OTLT and EAV combined you would normally insert data through a stored proc that tests values, types, data format, foreign keys etc.

I recently finished an OTLT/EAV project for interests sake just to see how far I could take it. It pulls data from loads of web pages, databases and files and generates a complete application from that data including feeds and integrity checking. I think it demonstrates a lot of the advantages of OTLT/EAV but would welcome some constructive feedback. The system currently shows country information but obviously it could be just about anything.

Mike

aschk · #14 (**permalink**) 06-21-07, 09:37

May I take a guess here (and by all means correct me if i'm wrong) that you don't program using object orientation or the mvc model, and most probably in a procedural fashion?

And going back to the point of the flame (seeing as we are in discussion now) say all our PCs (AND ONLY PCs) now have to have a vendor code applied to them, do you propose that we put another column in our table (for all million rows?) leaving a good proportion of them empty, or are you going to add a new row for each vendor code and somehow (using magic) tie that into the PCs only (maybe using our "param" description)?

Also, to cover the point about employees leaving a job and new people taking over. A good normalised structure (backed up by a nice E-R diagram) will make ANY database savvy programmer smile. Oooh look, the "CPU" table, i can get CPUs information there. Oh look, a "vendor" table, I can get Vendor information there.
As opposed to, oh look, i have ONE table... errr.... what are all the million param codes? Which ones are vendor codes? Which ones are CPU ids? Umm how do i find what i'm looking for? LOL@OLTL , nice posts rudy, i had a read of a couple of them

Incidently, for this bank project... what did your E-R & UML diagrams look like?

aschk · #15 (**permalink**) 06-21-07, 09:49

Oh, and what???

Quote:

In this case normalisation dictates you should have a new table for each new param on each new type

Draw it out... what is the relationship? One-to-One, One-To-Many, Many-To-Many ... it's not hard. A new "param" that is a single part of a PC (say adding a number for PCI slots in a PC) is nothing more than an additional column. Oh, and think of all the spaced spared without millions of null columns.

Internet Services

Web Development

Digital Marketing

Consumer Tech