Message
From: Richard Reina gatorre...@gmail.com
Reply-To: Richard Reina gatorre...@gmail.com
Date: 07/29/15 10:19 AM
To: mysql@lists.mysql.com mysql@lists.mysql.com
Cc:
Sub: table design question
If I were to create a database table(s) to tract most common repairs to
different appliances I
Hi Richard,
On 7/29/2015 10:19 AM, Richard Reina wrote:
If I were to create a database table(s) to tract most common repairs to
different appliances I can't decide if it would be better to create one
table with a long ENUM column that contains repairs that could be
attributed to any appliance
If I were to create a database table(s) to tract most common repairs to
different appliances I can't decide if it would be better to create one
table with a long ENUM column that contains repairs that could be
attributed to any appliance or different repair tables for each appliance.
All the
I have to keep this data in MySql, and i am not sure (as SQL/databse is not my
field) how to organise this into one or many tables? right now I would
represent my info as follows:
device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service
On 2012-08-15 1:54 PM, Rajeev Prasad wrote:
I have to keep this data in MySql, and i am not sure (as SQL/databse is not my
field) how to organise this into one or many tables? right now I would
represent my info as follows:
thx in advance.
Rajeev
From: Peter Brawley peter.braw...@earthlink.net
To: Rajeev Prasad rp.ne...@yahoo.com; mysql@lists.mysql.com
mysql@lists.mysql.com
Sent: Wednesday, August 15, 2012 4:01 PM
Subject: Re: suggestion needed for table design and relationship
rarely(almost never) changes and add happens only say twice a month.
So can I not have one big table like below?
Size is not the issue. A basic table design rule is atomicity--one value
per cell. Violating that rule screws up queries.
what disadvantage does it has? sorry I am not into RDBMS
at cardinality, and any field in which you can say, some
states may have more than one, put it in a separate table.
(One exception to cardinality-driven table design would be if a field is a
clearly defined, relatively unchanging set of constants. The classic example is
when different states
I want to create a US geography database. So far I have categories such as
state nick names (some states have more than one), state mottos (text 25 to
150 characters), state name origins (100-300 characters), state trivial
facts, entry into union. My question is; would it be better to keep at
-Original Message-
From: Richard Reina [mailto:gatorre...@gmail.com]
Sent: Monday, September 19, 2011 9:55 AM
To: mysql@lists.mysql.com
Subject: table design question
I want to create a US geography database. So far I have categories such as
state nick names (some states have more than
I would design three tables:
Table1 (states):
ID, name, abbreviation
Table2 (state_item):
ID, state_id (from states), item_id (from item_type), item_value (varchar)
Table3 (item_type):
ID, item_name
Into the item_type table you can insert:
Nick Name
Motto
Name origin
Facts
SomeOtherDataPoint
Thank you very much for all the insightful advice. I will keep the
separated.
2011/9/19 Jerry Schwartz je...@gii.co.jp
-Original Message-
From: Richard Reina [mailto:gatorre...@gmail.com]
Sent: Monday, September 19, 2011 9:55 AM
To: mysql@lists.mysql.com
Subject: table design
At 04:23 PM 9/9/2010, Tompkins Neil wrote:
Hi all,
Needing some advice on my tables design.
Basically I am designing a soccer application, and have a table which
contains player_bids (the values of which a player costs to be transferred
between clubs). Can someone please offer some input on
Hi all,
Needing some advice on my tables design.
Basically I am designing a soccer application, and have a table which
contains player_bids (the values of which a player costs to be transferred
between clubs). Can someone please offer some input on the best way in
which I should design the
Hi,
I'm creating a application which hosts football matches and I want to record
the player appearances, goals etc. I was thinking of having a record for
each player as follows :
appearance_id
season_id
player_id
team_id
competition_id
appearance
goals
yellow_card
red_card
date_played
Is this
HI,
I'm developing a cms, I need some suggessions regarding database design.
I'm creating role table in which role name will be unique, so my
question is that should I create roleid(int, autoincreament, primary
key )?
Same question for users table.
Note: I'll have user role mapping table.
--
Hi, I've the following table design (attached txt file, for some reason the
content was being blocked) and I'd like any advice if this is the correct
method/design.
Thanks for any comments. Neil
_
Play and win great prizes
Neil Tompkins wrote:
Hi,
I've the following table design (attached txt file, for some reason
the content was being blocked) and I'd like any advice if this is the
correct method/design.
TableName:ProductMaster
ProductMasterID
FriendlyProductName
TableName:ProductContent
ProductContentID
Hi wultsch,
Thanks a lot.
Every thing is going fine. I am only concerned with duplicate index, as it
is using disk space.
Is there any solution so that i can ignore duplicate index by altering the
table design. OR i have to end up with duplicate index.
Thanks,
Krishna Chandra Prajapati
On Thu
Krishna Chandra Prajapati schrieb:
Hi All,
Below is the table design on mysql server.
CREATE TABLE `coupon_per_course` (
`coupon_id` int(10) unsigned NOT NULL default '0',
`course_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`coupon_id`,`course_id`),
KEY
Hi All,
Below is the table design on mysql server.
CREATE TABLE `coupon_per_course` (
`coupon_id` int(10) unsigned NOT NULL default '0',
`course_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`coupon_id`,`course_id`),
KEY `idx_coupon_per_course` (`coupon_id`),
KEY
On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati
[EMAIL PROTECTED] wrote:
Hi All,
Below is the table design on mysql server.
CREATE TABLE `coupon_per_course` (
`coupon_id` int(10) unsigned NOT NULL default '0',
`course_id` int(10) unsigned NOT NULL default '0',
PRIMARY
Hello List,
If I have a table:
CREATE TABLE t (
id int(11) NOT NULL auto_increment,
fk1 mediumint(9) NOT NULL default '0',
fk2 smallint(6) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY idxfk1 (fk1,fk2),
UNIQUE KEY idxfk2 (fk2,fk1)
) TYPE=MyISAM;
I will about half the time have
In the last episode (Apr 24), Fan, Wellington said:
If I have a table:
CREATE TABLE t (
id int(11) NOT NULL auto_increment,
fk1 mediumint(9) NOT NULL default '0',
fk2 smallint(6) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY idxfk1 (fk1,fk2),
UNIQUE KEY idxfk2 (fk2,fk1)
?
-Original Message-
From: Dan Nelson [mailto:[EMAIL PROTECTED]
Sent: Monday, April 24, 2006 1:40 PM
To: Fan, Wellington
Cc: mysql@lists.mysql.com
Subject: Re: Table design; 2-column index
In the last episode (Apr 24), Fan, Wellington said:
If I have a table:
CREATE TABLE
In the last episode (Apr 24), Fan, Wellington said:
Thanks; I was really trying to ask about the potential performance
gain, however. I don't care so much about the UNIQUEness, but the
INDEXness.
See, I am wondering if I create an 2-column index wiht fk1 as the
first component, will that
Hi,
A very simple question. I have two products at our
website and i would like to keep track of how many of
each softwares were downloaded daily.
I am planning to create the following table:
id - auto_incr
date_of_download - data
product_name - enum value containing the two products
Then I
Hi All,
I'm not sure the best design approach for a product table for a number
of different hardware devices. Some devices have IP, Port, CPU, Memory
specs, whilst some don't. Current desing is below.
Products:
product_id
product_name
maker_id
controller_id
product_type_id
Mark Sargent [EMAIL PROTECTED] wrote on 06/03/2005 03:04:23 AM:
Hi All,
I'm not sure the best design approach for a product table for a number
of different hardware devices. Some devices have IP, Port, CPU, Memory
specs, whilst some don't. Current desing is below.
Products:
product_id
James Tu wrote:
Hi:
Let's say I want to store the following information.
Unique ID - INT(10) autoincrement
First Name - VARCHAR (25)
Last Name - VARCHAR (25)
Age - INT(3)
In general 'age' is a bad column, because you need to know what year the
data was entered to calculate the current age.
Hi:
Let's say I want to store the following information.
Unique ID - INT(10) autoincrement
First Name - VARCHAR (25)
Last Name - VARCHAR (25)
Age - INT(3)
Date - DATETIME
Activity - VARCHAR(100)
Data - TEXT
I would be basing my queries on all columns _except_ the Data column. I.e. I
would be
Hello,
Currently I am using syslog-sql to store syslog data in a mysql
database. The table format is something like:
ID hostnamefacilityprioritydatemessage
ID is auto incrementing
Now I am writting a perl app to calculate stats based on the data in the
table
In the last episode (Apr 16), Michael Gale said:
Currently I am using syslog-sql to store syslog data in a mysql
database. The table format is something like:
IDhostnamefacilityprioritydatemessage
Now I am writting a perl app to calculate stats based on the
Hello everybody
I'm designing a database for our new Application and have some
problems with the following:
We have a table Jobs in which we store all kind of Jobs.
Looks like this:
tbl_jobs
-
job_id, integer,
name, varchar,
description, varchar
easy so far :)
The problem is we also
Hi,
I'm designing a database for our new Application and have some
problems with the following:
We have a table Jobs in which we store all kind of Jobs.
Looks like this:
tbl_jobs
-
job_id, integer,
name, varchar,
description, varchar
easy so far :)
The problem is we also
On Apr 5, 2005 3:15 PM, Martijn Tonies [EMAIL PROTECTED] wrote:
Hi,
I'm designing a database for our new Application and have some
problems with the following:
We have a table Jobs in which we store all kind of Jobs.
Looks like this:
tbl_jobs
-
job_id, integer,
I'm designing a database for our new Application and have some
problems with the following:
We have a table Jobs in which we store all kind of Jobs.
Looks like this:
tbl_jobs
-
job_id, integer,
name, varchar,
description, varchar
easy so far :)
- Original Message -
From: Olivier Salzgeber [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, April 05, 2005 9:01 AM
Subject: problem with table design
Hello everybody
I'm designing a database for our new Application and have some
problems with the following:
We have
Hello,
I'll try to explain correctly my idea.
I need to develop a on-line sale's website. For that, I have some dough's on how to
create the table basket.
Client
- IDclie (PK)
- ...
Produts
- IDprod (PK)
- ...
Basket
- IDbask (PK)
- IDprod (FK)
- IDClie (FK)
- qt
- date
Sale
-
: RuiSMonteiro [mailto:[EMAIL PROTECTED]
Sent: 27 July 2004 09:44
To: [EMAIL PROTECTED]
Subject: MySQL table design
Hello,
I'll try to explain correctly my idea.
I need to develop a on-line sale's website. For that, I have some dough's on how to
create the table basket.
Client
- IDclie (PK
Hi,
I read it somewhere that InnoDB is faster for table with high read/write
concurrency. I have a table look like this:
CREATE TABLE diary (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
member_id INT UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
date DATE NOT NULL,
Carsten,
Thanks for the answer (and other thanks go to the other guys that answered
me).
I think normalization is the way to go. I think it is the right thing to
do (in theory). The problem is that theory doesn't fit all.
Basically I have some tables with only 2 fields (ID and name), and a
Ciprian Trofin writes:
Basically I have some tables with only 2 fields (ID and name), and a
central table, joined by a one-to-many relation. The key point here are the
2-field tables. If I keep them separate, I can extend them (add new fields)
without problem when need arise. But if there is no
Cities (CityID, Name)
People (PersonID, Name)
Travel_Exp (ExpID, Date, PersonID, Per_Diem)
Travel_Exp_Cities (CityID, ExpID)
Based on the descriptions I'd tend to go with a normalized table set
of this nature:
Cities (CityID, Name)
People (PersonID, Name)
Travel_Exp (ExpID, Date, PersonID,
Hi Ciprian,
OK, I'm by no means a DB guru, so a) take this with a grain of salt
and b) feel free to tear it apart if I'm completely wrong! ;]
If in fact your people and city tables aren't going to change very
often, then why don't you just go all the way and keep that
Hello,
I have the following structure:
people
-
| id | name |
-
| 1 | John |
| 2 | Mary |
-
cities
| id | city|
| 1 | Glasgow |
| 2 | Madrid |
| 3 | Berlin |
travel_expenditures
I have ip_address and ports that I want to use in my table. I was just going to
make each one a varchar. But was wondering if anyone has a better suggestion?
Should I use int for ports, which will have an index. Not sure how to store
ip_address.
This table has the possibility of having 800
rmck said:
I have ip_address and ports that I want to use in my table. I
was just going to make each one a varchar. But was wondering if
anyone has a better suggestion?
PostgreSQL ;-)
It has a native datatype for storing IP addresses. That means that
things like sorting and subnet inclusion
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jochem van Dieten wrote:
rmck said:
I have ip_address and ports that I want to use in my table. I
was just going to make each one a varchar. But was wondering if
anyone has a better suggestion?
PostgreSQL ;-)
It has a native datatype for
Mark Matthews wrote:
Jochem van Dieten wrote:
rmck said:
I have ip_address and ports that I want to use in my table. I
was just going to make each one a varchar. But was wondering if
anyone has a better suggestion?
PostgreSQL ;-)
It has a native datatype for storing IP addresses. That means that
rmck wrote:
I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion?
Should I use int for ports, which will have an index. Not sure how to store ip_address.
Use a varchar for the IP address.
% I don't know of a DB theory rule that says it's a bad idea to have the same
columns in many tables, but it might make the design more compact to take the
common stuff and put it into one
table.
Oh, there is, indeed. CF normalization :-)
Well of course, but that begs the question,
Greetings, my hands on school project is a small real estate database.
I am using MySQL 3.23 without InnoDB, but would like to migrate to 4.x
w/InnoDB at some point.
I would greatly appreciate any feedback on this design.
These are the business rules:
1) 1 matter may have several vendors
From: Paul Fine [EMAIL PROTECTED]
Date: 2003/11/28 Fri AM 11:14:25 CST
To: [EMAIL PROTECTED]
Subject: Please analyze my project table design
Greetings, my hands on school project is a small real estate database.
I am using MySQL 3.23 without InnoDB, but would like to migrate to 4.x
w
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi!
...and then [EMAIL PROTECTED] said...
%
...
% I don't know of a DB theory rule that says it's a bad idea to have the same columns
in many tables, but it might make the design more compact to take the common stuff and
put it into one table.
From: David T-G [EMAIL PROTECTED]
Date: 2003/11/28 Fri PM 03:49:17 CST
To: mysql users [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: Please analyze my project table design
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi!
...and then [EMAIL PROTECTED] said...
%
...
% I
with how I have setup the PK/FK/indices!
Thanks guys!
The repeated column names for example phone_number
-Original Message-
From: David T-G [mailto:[EMAIL PROTECTED]
Sent: Friday, November 28, 2003 3:49 PM
To: mysql users
Cc: [EMAIL PROTECTED]
Subject: Re: Please analyze my project table
% I don't know of a DB theory rule that says it's a bad idea to have the same
columns in many tables, but it might make the design more compact to take the
common stuff and put it into one table.
Oh, there is, indeed. CF normalization :-)
Well of course, but that begs the question, which
Paul,
Thanks guys. I thought that normalization would refer to redundant info not
necessarily the same column names? I would further see the problem with the
design if say phone_number in the agent table and lawyer table contained the
same data, which of course they won't.
The layout would
From: Stefan Hinz [EMAIL PROTECTED]
Date: 2003/11/28 Fri PM 04:45:24 CST
To: [EMAIL PROTECTED]
CC: David T-G [EMAIL PROTECTED], mysql users [EMAIL PROTECTED]
Subject: Re[3]: Please analyze my project table design
% I don't know of a DB theory rule that says it's a bad idea to have
From: Paul F [EMAIL PROTECTED]
Date: 2003/11/28 Fri PM 04:35:04 CST
To: 'mysql users' [EMAIL PROTECTED]
Subject: RE: Please analyze my project table design
Thanks guys. I thought that normalization would refer to redundant info not
necessarily the same column names? I would further
Hi all ,
I want to design a table, say
T_PRODUCT { PRD_ID, NAME, DESCRIPTION, PRICE }
where I want NAME and DESCRIPTION to have multi language inputed.
I searched before where found two solutions :
1)
T_PRODUCT {PRD_ID, PRD_DETAIL_ID, PRICE}
T_PRODUCT_DETAIL { PRD_DEATIL_ID, LANG, NAME,
I have a small table that contains company contact information:
Table: Contacts
Key
Name
Address
Phone
Email
Website
etc..
What I need to do is associate the type of industry the business servers
from a list of about 40 industries... My initial thinking was to create
another table that
PROTECTED]
Sent: Wednesday, October 29, 2003 1:09 PM
To: '[EMAIL PROTECTED]'
Subject: Table design help
I have a small table that contains company contact information:
Table: Contacts
Key
Name
Address
Phone
Email
Website
etc..
What I need to do is associate the type
Casey,
I would consider, on the most basic level, a method like this:
Contacts (all the company contact info, etc) using primary key contact_id -
IndustrialRelationships table
- industry_id primary key on Industries table (all the different type of
industries)
So, in the IndustrialRelationships
Hi All,
I'm designing a rather large database, and I'm concerned about performance.
Was wondering if anyone had any comments/advice?
The particular table I'm concerned about will have about 10 million lines,
each referencing a physical item I need to track. Each item in the table
will have a
I have to add group functionality to an existing database. The functionality
required will be something like /etc/group in linux. I have a need to create
and maintain a list of groups. Then, each group will have a list of members.
I will need to be able to search by member name and get a list of
On Wednesday 11 June 2003 08:47 pm, JJ wrote:
I have to add group functionality to an existing database. The
functionality required will be something like /etc/group in linux.
How about 3 tables. Groups, Members, and Relationships.
Table Group
id int auto_increment
name char
Table
On Wed, 11 Jun 2003 22:17:09 -0230, JJ
[EMAIL PROTECTED] wrote:
[...]
| ***
| * The second idea is to create two tables.
| ***
| table groups
| groupName varchar primary key
| groupDescription text
|
| table groupRelations
|
given this,
how would you write a query to list all members that are part of group_id=1
AND group_id=2 AND ... group_id 20. without having to write 20 JOIN
relationship r1 on r1.group_id =1 JOIN relationship r20 on r20.group_id
=20) wich gets extremely slow with large ammounts of
Table Group
id int auto_increment
name char
Table Member
groupID int auto_increment
name char
Table Relationship
group_id int
member_id int
i'd set it up like this
Table Group
id int auto_increment
name char
Table Member
userID int auto_increment
name char
groupID
Band members should probably be in a separate table.
One person may be a member of several bands, and
the membership in a band changes over time.
This will make modeling difficult.
[EMAIL PROTECTED] wrote:
Hello,
I`m doing a database in MySQL to catalog cds, and i`m not sure if my table
Hello,
I`m doing a database in MySQL to catalog cds, and i`m not sure if my table
structure is the best way to do it:
Artist Table
Artist_Id int unsigned not null auto_increment primary key
Name char(120) // Artist or Band Name
Country char(30) // Artist Or Band Country
Members char(255) //
First off, this post is going to be long :)
I have designed a database that tracks member earnings on the site. Currently, the
site has been operating for two weeks, has 10,000 members, and the earnings table
already has 750,000 rows. I ran a query to see how many rows were being added
First off, this post is going to be long :)
I have designed a database that tracks member earnings on the site. Currently, the
site has been operating for two weeks, has 10,000 members, and the earnings table
already has 750,000 rows. I ran a query to see how many rows were being added each
and MERGE could make queries against on the whole data set
run much faster.
Gordon Bruce
Interstate Software
A MySQL Training Consulting Partner
-Original Message-
From: Daren [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 23, 2002 4:45 AM
To: [EMAIL PROTECTED]
Subject: Table
Dear MySQL User,
I am sending the table schema of 5 tables.Here pid (partner ID) is in the top layer,
aid (Affiliate ID) is in the middle layer and uid (User Id OR Customer ID) is in the
bottem layer.User may promoted to Affiliate and affiliate may promoted to partner.We
have to store there
When you have a table with both numeric and variable-length text data,
and you need to update the numeric part a lot, it made sense in MyISAM
to split the numeric from the textpart. (Because working on fixed-length
tables is so much faster.) Say...
Original:
Table 1: id1 int, id2 int,
When you have a table with both numeric and variable-length text data,
and you need to update the numeric part a lot, it made sense in MyISAM
to split the numeric from the textpart. (Because working on fixed-length
tables is so much faster.) Say...
Original:
Table 1: id1, id2, articletext
I am creating site for greeting cards and need help desiging tables for DB
Currently I have three tables..
1) maincat which has following fields:
maincatid (autoincrement)
main_name (name of category)
2) subcat which has following fields:
subcatid (autoincrement)
maincatid
sub_name (name of sub
I had two questions about table design and format.
I'm designing an application that has two tables.
The first table will have
-Many selects that return about 20 results on average.
-Few inserts
-Very few UPDATEs
For this table it is not important that the newest inserts show up in selects
Hello all,
Sorry about the slightly off-topic request, but table designs structures are not
my strong point as of yet! :)
I have a project we are working on, and have a rough draft put together before
actually starting. I would greatly appreciate some feedback if improvements,
changes or
I have a general design question.
Is it better to design one table with 45 columns or to split it into 3
tables with 15 columns each. There would be about 5000 rows in the table and
it would be used more for reading rather than writing.
I'm new to this, is there anything I'm missing?
Thanks,
Hi,
How would the data look a like.
And how about the query's
Greetz Tbone
- Original Message -
From: "Nino Skilj" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 24, 2001 12:17 AM
Subject: Table Design -- which is better?
I have a general desig
The data would be 1's and 0's (on/off)
Nino
-Original Message-
From: Tbone [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 23, 2001 4:18 PM
To: [EMAIL PROTECTED]
Subject: Re: Table Design -- which is better?
Hi,
How would the data look a like.
And how about the query's
Greetz Tbone
85 matches
Mail list logo