ls.
>
> PB
>
> Original Message
> From: "Richard Reina"
> Reply-To: "Richard Reina"
> Date: 07/29/15 10:19 AM
> To: "mysql@lists.mysql.com"
> Cc:
> Sub: table design question
> If I were to create a database table(s) to tract most
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 or
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 tables
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
thx in advance.
Rajeev
From: Peter Brawley
To: Rajeev Prasad ; "mysql@lists.mysql.com"
Sent: Wednesday, August 15, 2012 4:01 PM
Subject: Re: suggestion needed for table design and relationship
On 2012-08-15 1:54 PM, Rajeev Prasad wrote:
> I
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:
device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specifi
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
|associated_
parate table for nick names.
I'd look carefully 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 se
Thank you very much for all the insightful advice. I will keep the
separated.
2011/9/19 Jerry Schwartz
> >-Original Message-
> >From: Richard Reina [mailto:gatorre...@gmail.com]
> >Sent: Monday, September 19, 2011 9:55 AM
> >To: mysql@lists.mysql.com
> >
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
S
>-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 name
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
le
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 th
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 finan
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 t
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.
--
Shar
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
Produc
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 p
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`
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
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`
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`
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 th
query?
> -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, Wellin
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 (
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
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 pla
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
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
product_model_numbe
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. I
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 u
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
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 w
- Original Message -
From: "Olivier Salzgeber" <[EMAIL PROTECTED]>
To:
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 followin
> > > 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
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
> > -
> >
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 probl
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
: 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"
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
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,
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
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
centr
>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, Ci
Carsten R. Dreesbach [mailto:[EMAIL PROTECTED]
Sent: 09 April 2004 00:52
To: Ciprian Trofin
Cc: [EMAIL PROTECTED]
Subject: Re: Best practice on table design
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
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
informatio
Hello,
I have the following structure:
people
-
| id | name |
-
| 1 | John |
| 2 | Mary |
-
cities
| id | city|
| 1 | Glasgow |
| 2 | Madrid |
| 3 | Berlin |
travel_expenditures
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 add
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 th
-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 da
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 inclu
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
>> >> % 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 t
>
> 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
>
> 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
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 woul
>> % 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 quest
very concerned 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 m
>
> 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
>
>
-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.
Oh,
>
> 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.
>
&
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 and/
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, DESCRIP
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 IndustrialRelationship
y, Casey [mailto:[EMAIL 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
>
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 contains
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 auto
This wouldn't work for a member belonging to several groups.
3 tables is the way to go.
Cordialement,
Damien COLA
-Original Message-
i'd set it up like this
Table Group
id int auto_increment
name char
Table Member
userID int auto_increment
name char
groupID
--
MySQL Gen
> 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 cha
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 JOINS.
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
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 Memb
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 wh
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
structure
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) // Ba
with
MyISAMPACK 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: [EM
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
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
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 bil
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, articlete
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
MyIS
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 ca
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 se
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 additio
Have you considered using the SET datatype? It would be perfect for your
situation.
- Original Message -
From: "Nino Skilj" <[EMAIL PROTECTED]>
To: "'Tbone'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, February 23, 2001 7:38
-
> 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
> ---
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'
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 g
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,
N
89 matches
Mail list logo
